A particular type of query is taking much much longer on 5.1 and 5.5 than it ever did on 5.0. Using a simplified query and an example database of around 700K records, it takes 6 seconds on 5.0.51, compared with up to 20 minutes on 5.1.63 and 5.5.24. On production data, it has gone from several seconds to several hours.
I expect it is due to changes in MySQL's subquery optimization. This is all on Ubuntu servers. Using MyISAM vs InnoDB doesn't seem to help.
I know the query can be rewritten, but that would mean reviewing and changing a ton of code. Is there anything else that can be done? Will MySQL 6 offer any relief?
Details below.
Given a table "a" and a child table "b" (representing a status log), the query returns all records in "a" and their most recent status.
SELECT a.id, (SELECT status FROM b WHERE b.a_id=a.id ORDER BY id DESC LIMIT 1) AS status FROM a;
DESC a;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
DESC b;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| a_id | int(11) | YES | MUL | NULL | |
| status | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
EXPLAIN SELECT a.id, (SELECT status FROM b WHERE b.a_id=a.id ORDER BY id DESC LIMIT 1) AS status FROM a;
+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | PRIMARY | a | index | NULL | PRIMARY | 4 | NULL | 65536 | Using index |
| 2 | DEPENDENT SUBQUERY | b | index | a_id | PRIMARY | 4 | NULL | 1 | Using where |
+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+
Example data is here if anyone wants to poke at this:
http://alandouglas.ca/example.sql.gz (3.2MB)
Thanks.