One of the concats used in this module is missing a cast on nid, this implicitly converts the string part of the concat to binary and means this can't use the index on the table. Here is a before and after the patch:
mysql> explain SELECT n.nid, n.type, n.status, n.promote, n.changed, u.dst FROM node n LEFT JOIN url_alias u ON u.src=CONCAT('node/',n.nid);
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | n | ALL | NULL | NULL | NULL | NULL | 4246 | |
| 1 | SIMPLE | u | ALL | src | NULL | NULL | NULL | 8152 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
change to
mysql> explain SELECT n.nid, n.type, n.status, n.promote, n.changed, u.dst FROM node n LEFT JOIN url_alias u ON u.src=CONCAT('node/',CAST(n.nid as CHAR));
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | n | ALL | NULL | NULL | NULL | NULL | 4246 | |
| 1 | SIMPLE | u | ref | src | src | 386 | func | 1 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
Let me know if I'm missing something obvious here :)
| Comment | File | Size | Author |
|---|---|---|---|
| urllist.module.diff | 772 bytes | nnewton |
Comments
Comment #1
deekayen commentedThanks. Committed to 46, 47, 5, 6, and HEAD.