Filesort invoked when ORDER BY clause used on a text field in MySql

(written by lawrence krubner, however indented passages are often quotes). You can contact lawrence at: lawrence@krubner.com, or follow me on Twitter.

A nice example of how not to do things, someone used a text field to store dates.

mysql> CREATE INDEX game_id_date ON hints (game_id, date(10));
Query OK, 1001 rows affected (0.04 sec)
Records: 1001 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM hints WHERE game_id = 374 ORDER BY date DESC;

+——-+——+—————+————–+———+——-+——+—————————–+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+——-+——+—————+————–+———+——-+——+—————————–+
| hints | ref | game_id_date | game_id_date | 5 | const | 1 | Using where; Using filesort |
+——-+——+—————+————–+———+——-+——+—————————–+

Success: No table scan and only one row examined. However, Extra doesn’t say “Using index” even though our multi-column key includes both game_id and date. MySQL won’t retrieve the values from the index because “SELECT * ” requires more values: It requires all 6 columns for every matching row but the index only has 2 columns. In Case 1 only columns that were part of the key were included in the SELECT criteria, which is why things worked out better there than here. If we alter the table we can at least get rid of the filesort. The filesort is caused by date being TEXT. If we drop the index, normalize the data, change the column type to DATE, and re-add the index (these commands left out for brevity):

mysql> EXPLAIN SELECT * FROM hints WHERE game_id = 374 ORDER BY date DESC;

+——-+——+—————+————–+———+——-+——+————-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+——-+——+—————+————–+———+——-+——+————-+
| hints | ref | game_id_date | game_id_date | 5 | const | 1 | Using where |
+——-+——+—————+————–+———+——-+——+————-+

Not a slow query anymore. And it goes to show that using efficient column types is important, unless you like filesorts.

Post external references

  1. 1
    http://hackmysql.com/case2
Source