FYI: poor mysql query planning when ordering by id

Paying attention to slow queries today Bob, Nick, and I saw a number of seemingly innocuous-looking sql statements that were taking a ridiculously long time to execute. For example:

SELECT * FROM `msgs` WHERE address_id = 11295 ORDER BY id DESC LIMIT 1;

There's an index on address_id so there's no reason this should take up to 8 seconds! Explaining itl:

mysql> explain SELECT * FROM `msgs` WHERE address_id = 11295 ORDER BY id DESC LIMIT 1;
+----+-------------+-------+-------+--------------------------------------------------------------------------------------------------+---------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys                                                                                    | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+--------------------------------------------------------------------------------------------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | msgs  | index | index_msgs_on_address_id_and_incoming,index_msgs_on_address_id_and_recognized_cmd_and_created_at | PRIMARY | 4       | NULL | 75525 | Using where |
+----+-------------+-------+-------+--------------------------------------------------------------------------------------------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)

It's using the id as the key for this query because of the order by, resulting in a 75k row scan. Adding an additional order by (should be equivalent) solves the problem:

mysql> explain SELECT * FROM `msgs` WHERE (`msgs`.address_id = 11295) ORDER BY id DESC, created_at DESC LIMIT 1;
+----+-------------+-------+------+--------------------------------------------------------------------------------------------------+---------------------------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys                                                                                    | key                                   | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+--------------------------------------------------------------------------------------------------+---------------------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | msgs  | ref  | index_msgs_on_address_id_and_incoming,index_msgs_on_address_id_and_recognized_cmd_and_created_at | index_msgs_on_address_id_and_incoming | 5       | const |    9 | Using where; Using filesort |
+----+-------------+-------+------+--------------------------------------------------------------------------------------------------+---------------------------------------+---------+-------+------+-----------------------------+

I'm fixing up the two specific places I noticed this (msgs, routing_suggestion_requests), but it's something to be on the lookout for because we order by id all over the place.

I'll be paying much more attention to slow queries now that we have splunk in place.

--
Love,
Fritz