Mysql – Simplest MySql query uses filesort and ignores created_at index

indexMySQLoptimizationperformancequery-performance

I have the simplest possible MySql query and am confused about why it's using filesort instead of relying on my index.

EXPLAIN SELECT 
    id,
    created_at
FROM
    contacts
ORDER BY created_at DESC;

This result shows that it uses the index:

+----+-------------+----------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | contacts | NULL       | index | NULL          | contacts_created_at_index | 5       | NULL |  377 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+

But then look at this next query (the same but now also has emailAddress):

EXPLAIN SELECT 
    id,
    created_at,
    emailAddress
FROM
    contacts
ORDER BY created_at DESC;

Why does its result say "Using Filesort"?

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | contacts | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  377 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+

I've already read ORDER BY Optimization and Index Hints from the MySQL documentation.

Adding the hint USE INDEX FOR ORDER BY(contacts_created_at_index) doesn't help.

My table has many more columns than these. The id is primary key, and created_at has a basic index, and emailAddress has a unique index, and there are other columns.

I'm using this table in Laravel 5.4.

Best Answer

  • With InnoDB, any secondary index (such as INDEX(created_id)) implicitly includes a copy of the PRIMARY KEY (id). Hence, the first query is "covering" as indicated by "Using index".
  • When you need to look at more than about 20% of a table, it is actually faster to ignore the index, and do a full table scan -- rather than bouncing between the index and the data. (The index and the data are separate BTree structures, connected by that PK copy.)
  • ORDER BY causes a filesort unless it can be consumed by the index it is using. (But it is not using it, as the above point points out.)
  • MySQL rarely uses more than one index in a SELECT. (Because using multiple indexes would be more complex, and almost always slower.)

That is just a few of the possible things that can go on (other than the items I just mentioned).

See also my Index Cookbook .