EXPLAIN everything, part 2

I previously wrote a post urging people trying to optimize their database usage to run the "EXPLAIN" command on everything. This is easy advice to ignore in the case of simple queries, but even the simple queries can be big performance problems if you miss an important detail.

The other day, a friend was complaining of slow queries and was looking for some help fixing them. The first step we took was to take the "problem page" and enable debug output that printed each query that was run along with the number of milliseconds each took. If you don't have a facility for easily doing this (at the very least on a staging or development server) then you are essentially wandering in the dark for optimization.

Looking at the debug output, we found something surprising. The slowest query on the page was the seemingly innocuous:

SELECT `User`.`id` FROM `users` AS `User` WHERE `opensocial_id` = 219771253 LIMIT 1;

My friend was sure there was an index on User.opensocial_id yet the debug output showed that it took more than a second. With an index, this should take less than a millisecond. I asked him to run an EXPLAIN on it, and we saw:

mysql> EXPLAIN SELECT `User`.`id` FROM `users` AS `User` WHERE `opensocial_id` = 2778153 LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | User  | ALL  | opensocial_id | NULL | NULL    | NULL | 272877 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

The entry in the possible_keys column confirmed that there indeed was an index on opensocial_id. However, NULL in the key column meant that mysql was deciding the opensocial_id key was either unusable or didn't have good enough selectivity to choose over a full table scan. The high value in the rows column along with Using where under Extra indicated that mysql was reading through every row in the table, running a comparison, and outputting those rows for which the condition matched.

So, the next question was why mysql was deciding not to use the index for this query. Doing a SHOW CREATE TABLE User revealed that the opensocial_id column was of type varchar(30). Because the column was a varchar, mysql couldn't use an index on it to do a comparison against a numeric query. You can see the reason for
this:

mysql> select "0234" = 234\G
*************************** 1. row ***************************
"0234" = 234: 1

mysql> select "234.0" = 234\G
*************************** 1. row ***************************
"234.0" = 234: 1
1 row in set (0.00 sec)

For any given numeric value in the query, there are several varchar representations that are "equal" to it (shown as value 1 above). As such, there is no way for mysql to do an index lookup on a varchar column to satisfy an equality condition against a numeric constant.

One easy solution would be to change the varchar(30) to a numeric type. Unfortunately, it turns out that opensocial user identifiers are not simple 64-bit numeric values -- depending on the opensocial platform provider they are sometimes zero-padded, sometimes not, etc. So, using a bigint unsigned was not an option.

What was the solution, then? We simply added quotes to the query:

SELECT `User`.`id` from `users` as `User` WHERE `opensocial_id` = '23424234' LIMIT 1;

This made the equality comparison between two varchars, and the index was used. The query now runs consistently in less than a few milliseconds.

In summary, EXPLAIN everything! Even the queries that look incredibly simple can be incredibly slow if they're not using the index you think they are.

Always EXPLAIN your queries

One of the first tips if you're working on improving your database speed is to always run EXPLAIN on your queries. This lets you see the plan of execution that MySQL will take, and evaluate whether and how your indexes will be used.

Usually I only bother to take my own advice for complicated queries involving GROUP BY, subselects, etc. Tonight, though, I was working on optimizing some of our pages by moving data into summary tables, and a query took a lot longer than I expected:

mysql> select SQL_NO_CACHE s.title, ar.name from SongBuzz sb JOIN Song s ON sb.song_id = s.id JOIN Album al ON s.album_id = al.id JOIN Artist ar ON al.artist_id = ar.id ORDER BY sb.recs_1w desc limit 3;
+----------------------+------------------+
| title                | name             |
+----------------------+------------------+
| Deep Sea Green       | Mandyleigh Storm | 
|  Suffer For Fashion  | of Montreal      | 
| Just Enough          | Dan Tharp        | 
+----------------------+------------------+
3 rows in set (1.17 sec)

All of the foreign key fields in the JOIN were indexed, as was sb.recs_1w. There's no reason this query should take 1.2 seconds when the entire data fits easily in RAM. So I ran EXPLAIN:

mysql> EXPLAIN select SQL_NO_CACHE s.title, ar.name from SongBuzz sb JOIN Song s ON sb.song_id = s.id JOIN Album al ON s.album_id = al.id JOIN Artist ar ON al.artist_id = ar.id ORDER BY sb.recs_1w desc limit 3;
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------------+-------+---------------------------------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref                     | rows  | Extra                           |
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------------+-------+---------------------------------+
|  1 | SIMPLE      | ar    | ALL    | PRIMARY           | NULL      | NULL    | NULL                    | [redacted big number] | Using temporary; Using filesort | 
|  1 | SIMPLE      | al    | ref    | PRIMARY,artist_id | artist_id | 4       | amiest_production.ar.id |     1 | Using index                     | 
|  1 | SIMPLE      | s     | ref    | PRIMARY,album_id  | album_id  | 4       | amiest_production.al.id |     4 |                                 | 
|  1 | SIMPLE      | sb    | eq_ref | PRIMARY           | PRIMARY   | 4       | amiest_production.s.id  |     1 |                                 | 
+----+-------------+-------+--------+-------------------+-----------+---------+-------------------------+-------+---------------------------------+
4 rows in set (0.02 sec)

From this query plan, it was clear that MySQL was first reading the artist table, then joining to find all the albums, then joining to find all the songs, then joining against the SongBuzz summary table. After getting all these rows, it was sorting the whole thing in a temporary table. The more logical plan would be to sort the SongBuzz table by recs_1w, since that column is already indexed, grab the top 3 rows, and then join through to find the song and artist names. Using the STRAIGHT_JOIN flag to the SELECT statement, we can force this:

mysql> explain select SQL_NO_CACHE STRAIGHT_JOIN s.title, ar.name from SongBuzz sb JOIN Song s ON sb.song_id = s.id JOIN Album al ON s.album_id = al.id JOIN Artist ar ON al.artist_id = ar.id ORDER BY sb.recs_1w desc limit 10;
+----+-------------+-------+--------+-------------------+---------+---------+--------------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref                            | rows   | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+--------------------------------+--------+-------------+
|  1 | SIMPLE      | sb    | index  | PRIMARY           | recs_1w | 2       | NULL                           | [redacted big number] | Using index | 
|  1 | SIMPLE      | s     | eq_ref | PRIMARY,album_id  | PRIMARY | 4       | amiest_production.sb.song_id   |      1 |             | 
|  1 | SIMPLE      | al    | eq_ref | PRIMARY,artist_id | PRIMARY | 4       | amiest_production.s.album_id   |      1 |             | 
|  1 | SIMPLE      | ar    | eq_ref | PRIMARY           | PRIMARY | 4       | amiest_production.al.artist_id |      1 |             | 
+----+-------------+-------+--------+-------------------+---------+---------+--------------------------------+--------+-------------+

and the resulting query executes somewhere around 60x faster:

mysql> select SQL_NO_CACHE STRAIGHT_JOIN s.title, ar.name from SongBuzz sb JOIN Song s ON sb.song_id = s.id JOIN Album al ON s.album_id = al.id JOIN Artist ar ON al.artist_id = ar.id ORDER BY sb.recs_1w desc limit 3;
+----------------------+------------------+
| title                | name             |
+----------------------+------------------+
| Deep Sea Green       | Mandyleigh Storm | 
|  Suffer For Fashion  | of Montreal      | 
| Just Enough          | Dan Tharp        | 
+----------------------+------------------+
3 rows in set (0.02 sec)