0

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.

  • So, do you need all the rows of the table returned? If the table has a million rows? If it has 100 millions? – ypercubeᵀᴹ May 21 '17 at 20:17
  • In other words, now that the table has a tiny 400 rows, why do you care what index is used? I would bet it's fast anyway. And sorting 400 rows can't be a problem. – ypercubeᵀᴹ May 21 '17 at 20:19
  • @ypercubeᵀᴹ It will never have millions of rows. Only a few thousand. And it's already slower than I'd have guessed: 0.844 sec. It should definitely be faster. – Ryan May 21 '17 at 21:41
  • @Ryan how much faster should it be? – Erik Darling May 22 '17 at 1:48
  • @sp_BlitzErik I'm not very familiar with how fast queries should be, but I could have sworn the MySql databases I've worked on always returned simple queries in ~0.1 sec or less. I wonder if this Cloudways server I'm now using is somehow less capable / slower than I'm used to. – Ryan May 22 '17 at 3:02
1
  • 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 .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.