The path whitelist rebuilding code has a very interesting little query:

SELECT SUBSTRING_INDEX(source, '/', 1) AS path FROM {url_alias} GROUP BY path

This is SQL-92 compliant (SQL-92 only allow GROUP BY from column names or aliases and ordinal positions), but it is not supported neither by Oracle nor by SQL Server.

I suggest we rewrite it as a DISTINCT query, which is what we actually mean here anyway.

CommentFileSizeAuthor
#1 03-770664-path-group-by-alias.patch878 bytesDamien Tournoud
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented
Status: Active » Needs review
FileSize
03-770664-path-group-by-alias.patch878 bytes

Something like this will do.

moshe weitzman’s picture

moshe weitzman CreditAttribution: moshe weitzman commented

Is there any performance impact here?

moshe weitzman’s picture

moshe weitzman CreditAttribution: moshe weitzman commented
Status: Needs review » Reviewed & tested by the community

The meaning is unchanged.

Dries’s picture

Dries CreditAttribution: Dries commented
Status: Reviewed & tested by the community » Fixed

Committed to CVS HEAD. Thanks.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

catch’s picture

catch
he/him
Primary language English
CreditAttribution: catch commented

I just spotted this after seeing the query was different between Pressflow and D7/8.

Better late than never with the performance comparison:

This is on a Drupal 6/Pressflow site with millions of path alias.

SELECT SUBSTRING_INDEX(path, '/', 1) AS path FROM menu_router GROUP BY path;


mysql> EXPLAIN SELECT SUBSTRING_INDEX(src, '/', 1) AS path FROM url_alias GROUP BY path;
+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | url_alias | index | NULL          | src  | 386     | NULL | 4586690 | Using index; Using temporary; Using filesort |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+

mysql> EXPLAIN SELECT DISTINCT SUBSTRING_INDEX(src, '/', 1) AS path FROM url_alias;
+----+-------------+-----------+-------+---------------+------+---------+------+---------+------------------------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows    | Extra                        |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+------------------------------+
|  1 | SIMPLE      | url_alias | index | NULL          | src  | 386     | NULL | 4586690 | Using index; Using temporary |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+------------------------------+

Both queries come in at 4.91 seconds for me.