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

chx’s picture

chx CreditAttribution: chx commented 
SELECT IFNULL(us.dst, un.dst) AS dst
FROM {url_alias} u
LEFT JOIN {url_alias_specific} us ON u.src = us.src AND us.language = :language
LEFT JOIN {url_alias_neutral} un ON u.src = un.src AND us.src IS NULL
WHERE u.src = :src

So 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.

chx’s picture

chx CreditAttribution: chx commented

Damien 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.

chx’s picture

chx CreditAttribution: chx commented

On COALESCE support:

that seems promising.

chx’s picture

chx CreditAttribution: chx commented

Probably 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.

Gábor Hojtsy’s picture

Gábor Hojtsy
he/him
Primary language Hungarian
Location Hungary
CreditAttribution: Gábor Hojtsy commented

Note 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.

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented

chx 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:

          $map[$path_language] = db_query("SELECT src, dst FROM {url_alias} WHERE src IN(:paths) AND language IN(:language, '') ORDER BY language ASC", array(
            ':system' => $system_paths,
            ':language' => $path_language
          ))->fetchAllKeyed();

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.

chx’s picture

chx CreditAttribution: chx commented

catch, 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.

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented

I suggest storing every aliases from every langage in the menu tree cache, thus avoiding the "a menu tree cache per language" alternative.

chx’s picture

chx CreditAttribution: chx commented

I 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.

stephencamilo’s picture

stephencamilo CreditAttribution: stephencamilo as a volunteer commented
Issue summary: View changes
Status: Active » Closed (won't fix)
hestenet’s picture

hestenet
He/Him
Location Portland, OR 🇺🇸
CreditAttribution: hestenet at Drupal Association commented
Status: Closed (won't fix) » Active

Reset issue status.