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
INDEX(created_id)
) implicitly includes a copy of thePRIMARY KEY
(id
). Hence, the first query is "covering" as indicated by "Using index".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.)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 .