Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
Since we added a language column to url_aliases, it is impossible to JOIN against the table or even just to query for more than alias at once. To recap, SELECT src FROM {url_alias} WHERE dst = :dst AND language IN(:language, '') ORDER BY language DESC we use this query. We need a better storage.
Comments
Comment #1
chx CreditAttribution: chx commentedSo the url_alias would store every src possible. To search by dst, I presume we would need another table which only stores every possible dst.
Comment #2
chx CreditAttribution: chx commentedDamien has a lot better idea: per language columns. So have a dst and a dst_en and a dst_es and so on. merlinofchaos suggest using COALESCE as the operation. So SELECT COALESCE(dst_en, dst) FROM url_alias WHERE src = :src. We could construct this statement once per page request (and per language) and re-execute with different :src as necessary. To search for the source of a destination, I recommend SELECT src FROM url_alias WHERE dst_en = :dst OR dst:dst and resolving in PHP -- this way the SELECT is fast and sourcing is rare anyways.
Comment #3
chx CreditAttribution: chx commentedOn COALESCE support:
that seems promising.
Comment #4
chx CreditAttribution: chx commentedProbably it's not clear why i want this -- I want to join the menu_links table to url_alias to avoid a multitude of path lookups.
Comment #5
Gábor HojtsyNote that having a column per language requires a "fluid" schema which modifies itself based on clicks on the admin UI. There was no precedent for this in Drupal 6 core (each Drupal 6 core table I know of has a fixed schema), but given fields in Drupal 7, this is now a known concept in Drupal 7 core.
Comment #6
catchchx pointed me to this issue in irc. Now that #456824: drupal_lookup_path() speedup - cache system paths per page. is in, we still have work to do improving performance on pages where the cache isn't primed, and reducing the size of the cache entries as well.
Between this issue and #106559: drupal_lookup_path() optimization - skip looking up certain paths in drupal_lookup_path() there's potentially quite big savings to be made. Here's a summary of what I understand this issue to be about:
[chx] catch: currently we store src,dst, language
[chx] catch: i want to store src,dst, dst_en, dst_sp , dst_de etc
So for every language that's added, we dynamically add a column (and index) to the url_alias table. Then in menu_tree_page_data() the path aliases are looked up for each visible menu (by adding a join to the existing query) and cached in the existing cache_menu() entries per page. This means no drupal_lookup_path() queries for menu links at all, ever.
While I'm thinking about this though, a couple of potential issues:
1. We'd need change the menu cache to be per-language-per-page instead of just per-page - this might outweigh having smaller cache_path entries on multilingual sites
2. We'll have to clear the cache on every path_set_alias()
Also I'm not sure if the problem of multiple single aliases for one path isn't solved by:
Ordering by ASC and overwriting array keys gives us the same precedence we have now for alases.
Yes it's an extra query and a foreach, but it'd save the dynamic schema and it'd be cached anyway.
Comment #7
chx CreditAttribution: chx commentedcatch, you cant join the current storage. clearing the menu cache on every path_set_alias can be avoided if we make sure to only clear when the system path existed before. New users and nodes do not need to clear the cache.
Comment #9
Damien Tournoud CreditAttribution: Damien Tournoud commentedI suggest storing every aliases from every langage in the menu tree cache, thus avoiding the "a menu tree cache per language" alternative.
Comment #10
chx CreditAttribution: chx commentedI am also wondering whether it'd be better to use separate tables per language. Thing is, an ALTER TABLE on url_alias might not really be practical -- it can take several minutes for that query to run. Another train of thought would be that a new language is a whole rollout anyways, with new content and whatnot but still I cringe about the necessity of ALTERing such a big table. Copying the table structure, LEFT JOINing on src and COALESCE'ing would work... sourcing would require two queries, one for the language specific version, one for not, but those are against an index and sourcing is quite rare.
Comment #11
stephencamilo CreditAttribution: stephencamilo as a volunteer commentedComment #12
hestenetReset issue status.