Query count exceptionally high

Having the same issue in 1.3 too...

@mattjones86 did you make any headway on this?

I've implemented the static caching for lookup and noPath (like the AliasManager) does...

This brought the homepage down from 5788 queries to 3871 (and knocked 2 seconds off)... But thats still about 3,500 queries too many ;)

The "extend" modules page was 18k queries and thats down to 11k now, too... but that take 45 seconds to load.

(all the above is on a local ddev instance).

Very much work in progress....

I've also just hugely helped the DB by adding a new index on alias, domain_id and language. This didn't help the NUMBER of queries, but it has hugely helped the time; page is down to 1.5s now and DB query time from 2.5s to 0.5s.

Looked onto per-page caching (which is the next massive win, really) and its non-trivial to add (needs new writeCache and key generation + an event listener to write the cache on page close).

Will re-roll the patch in a bit...

Just looked at the query itself... its really odd... why does it return the same ID field twice in two different field names... and why does it join the domain_path table to itself by ID?

Revised patch - this one includes the index.

Hmmm - the noAlias stuff isn't right and this has also included some language selection fixes we were using in https://www.drupal.org/project/domain_path/issues/3245281#comment-15102240 🐛 Wrong Language is chosen and no node visibe under certian conditions Needs work but no longer seem to behave the same.

This, so far, seems more reliable for me. Fixed the issue where it was returning the wrong content for the domain.

Also fixes the "no result" cache, which brings out homepage down to 2569 queries (from the original ~5,700).. Query time is down to 400ms and page render to 1.3s (on ddev locally as admin - which is doing a lot of other lookups for admin paths, etc).

I guess we could do a separate module which adds the `/admin` path to the whitelist (I'm aware there is an issue open to rename this, I'm using it for consistency with the current codebase) - this might help further, but would mean you couldn't alias any admin URLs (which is probably a rare thing anyway).

+++ b/src/Entity/DomainPath.php
@@ -23,6 +23,7 @@ use Drupal\domain_path\DomainPathInterface;
+ *     "storage_schema" = "Drupal\domain_path\DomainPathStorageSchema",

Looks promising. But the DomainPathStorageSchema file is not included in your patch.