The 2026 Drupal Business Survey is open. Your response will help shape the future of the Drupal business ecosystem.I could use some help from some awesome DB people to make sure the following query gets optimized:
SELECT COALESCE(ua.dst, x.loc) AS alias, x.loc, x.lastmod, x.changefreq, x.changecount, x.priority FROM {xmlsitemap} x
LEFT JOIN (SELECT src, dst FROM {url_alias} WHERE language IN ('en', '') GROUP BY src ORDER BY language) ua ON x.loc = ua.src
WHERE x.status = 1This is what I get when I use EXPLAIN on the query with MySQL:
+----+-------------+------------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------------------------------------------+
| 1 | PRIMARY | x | ALL | NULL | NULL | NULL | NULL | 285 | Using where |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 20051 | |
| 2 | DERIVED | url_alias | ALL | NULL | NULL | NULL | NULL | 20053 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------------------------------------------+
...which doesn't look the best. The problem seems to be the GROUP BY. Any advice or suggestions?
Comments
Comment #1
giorgio79 commentedThe whole LEFT JOIN WHERE seems to be required only by multilanguage sites? Why filter for EN language for single language site.
Also GROUP BY src assumes there could be multiple occurrences of src, but I thought that is not allowed, and src dst relationship is 1 to 1 meaning 1 src is assigned to 1 dst.
So not sure if GROUP BY is needed at all.
Comment #2
avpadernoThere could be an alias for each language; in that case more than one alias would have the same value for the field src.
Comment #3
giorgio79 commentedThanks Kiam, I though different language node occurrences get a different node id, not sure.
But the LEFT JOIN part could easily be dismissed in that case for single language sites.
Comment #4
avpadernoThere are no restrictions on assigning two different aliases to the same node, where one would be used for one language, and the other for a different language. The code tries to be as more generic as possible.
I actually don't see any reason to make the query deal with the path alias too.
The purpose of doing that should be to avoid to execute two different queries, which would be replaced by a single query. The problem is that the query is getting a value that is not required until the node URL is generated.
Rather than getting the path alias, and then instruct
url()that it must not find the path alias, it would maybe be better to not find the path alias, and leturl()do that task.Comment #5
dave reidIt is wise to do a left join on {url_alias} because we pass all the links through url(). If we don't already have the alias for each path, *each* and *every* call to url() results in a call to drupal_get_path_alias() and drupal_lookup_path(), which results in the following query being executed *each* and *every* time:
SELECT dst FROM {url_alias} WHERE src = '%s' AND language IN('%s', '') ORDER BY language DESC. Would you prefer one join, or still one big SQL and 50,000 (max) other SQL calls for each sitemap chunk?We need the GROUP BY because there is no restriction on the number of aliases a path can have, even if you have multiple languages disabled. If we didn't have that we would end up with listing every single alias in the sitemap, which is a no-no.
Comment #6
dave reid@giorgio79: The LEFT JOIN on url_alias is required because we want to look up all the url aliases instead of looking each one up invididually in url(). It doesn't necessarily have anything to do with multilingual sites, just url aliases. It just so happens that it's pretty much the same query used to look up path aliases in drupal_lookup_path(), but used in a join.
Comment #7
Anonymous (not verified) commentedThen you need a distinct on the selection or you end up with more than one of the same src anyway. And which alias for the same src is the correct one to use? I thought it was the last entry, don't we need to order by the pid in descending order?
The order by language looks needless since we're only selecting a language of 'en' or ''.
Comment #8
dave reid@earnie: Core's drupal_lookup_path() does not sort by pid DESC, so I didn't want to change the expected behavior. Using GROUP BY works perfectly to eliminate the duplicate src values. I couldn't get it to work with DISTINCT, so if you can figure it out, please let me know.
Also, replace 'en' with the current language of the sitemap being generated since we have multilingual support. Technically it's a placeholder value '%s', so it changes. The order by language is currently used by drupal_lookup_path so in case there are two aliases, one for all languages and one for english language, the alias that is used is the one for all languages since it is an empty string.
Comment #9
dave reidFor reference, we want to closely duplicate the SQL that drupal_lookup_path() runs (see #5), but in a join.
Comment #10
avpadernoIf the
LEFT JOINis kept, then there isn't a way to replace theGROUP BY, which is needed to avoid duplicates for src values. Being this the case, nothing in the query can be removed, or replaced.Comment #11
dave reidLooks like removing the subquery select and just running a left join on the url_alias table directly helps a bit. My average query went from 1.5 seconds to 0.01 seconds.
Still says that "Using where; Using temporary; Using filesort" for the {xmlsitemap} table. :/
Comment #12
dave reidI ♥ PostgreSQL. I can't just do
GROUP BY x.loc. PostgreSQL requires all fields to be listed (GROUP BY x.loc, ua.dst, x.lastmod, x.changefreq, x.changecount, x.priority. Bleh.Comment #13
Anonymous (not verified) commentedIIRC, Oracle needs all the selected fields listed as well but today my memory is fuzzy.
Comment #14
dave reidI gave up for the moment trying to do a left join on {url_alias}. The query is now:
SELECT x.loc, x.lastmod, x.changefreq, x.changecount, x.priority FROM {xmlsitemap} x WHERE x.status = 1and each $link['loc'] is run through url(). I'll probably figure this out soon, but it was getting too damn irritating.Comment #15
dave reidI have given up trying to do a database join attempt to fetch all the necessary url aliases. Instead I implemented #489006: Optimize fetching url aliases, so I'm considering this won't fix since I don't believe it can be optimized any more since we're just selecting a few fields from one table without any orders or groups.