Why does MySQL's `explain` not say `filesort` when it's using `filesort`?

Question

I have a query that looks something like:

SELECT * FROM tablename WHERE condition = 1 ORDER BY id LIMIT 1000;

id is the primary key. I have an index on condition and an index on id (I didn't add a composite index on condition and id yet). This query uses only the condition index, not the id index.

If I explain the query, it just says Using where. I expected it to say filesort. However, since this query is sorting without using an index, it must be using filesort. In addition, this query is timing out, which is another clue that it's using filesort. If I run the query without the order by, it doesn't time out.

Why does it sometimes not say filesort? I think it should be using filesort in both cases, since the query's so slow.

Answer 1

I suspect the issue is that you're ordering by id. If the id is an auto incremented integer it's probable the table is already ordered by id. For example...

mysql> describe tablename;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) | NO   | PRI | NULL    | auto_increment |
| value  | int(11) | YES  | MUL | NULL    |                |
| status | int(11) | YES  | MUL | NULL    |                |
+--------+---------+------+-----+---------+----------------+

mysql> show indexes from tablename;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tablename |          0 | PRIMARY  |            1 | id          | A         |      199824 |     NULL | NULL   |      | BTREE      |         |               |
| tablename |          1 | value    |            1 | value       | A         |      101829 |     NULL | NULL   | YES  | BTREE      |         |               |
| tablename |          1 | status   |            1 | status      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from tablename where status = 1 order by id limit 1000;
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows  | filtered | Extra                 |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+-----------------------+
|  1 | SIMPLE      | tablename | NULL       | ref  | status        | status | 5       | const | 99912 |   100.00 | Using index condition |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

When ordering by id, no filesort. Watch what happens when we order by another indexed column...

mysql> explain select * from tablename where status = 1 order by value limit 1000;
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+---------------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows  | filtered | Extra                                 |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | tablename | NULL       | ref  | status        | status | 5       | const | 99912 |   100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+-------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

There's the filesort you're expecting.

---

In fact, select ... and select ... order by id both return in the same order. order by id is an implicit ordering.

mysql> SELECT * FROM tablename WHERE status = 1 order by id LIMIT 1000,10;
+------+-------+--------+
| id   | value | status |
+------+-------+--------+
| 1935 |    12 |      1 |
| 1939 |    59 |      1 |
| 1940 |    56 |      1 |
| 1941 |    21 |      1 |
| 1942 |     5 |      1 |
| 1943 |    68 |      1 |
| 1944 |    65 |      1 |
| 1947 |    27 |      1 |
| 1948 |    44 |      1 |
| 1950 |    75 |      1 |
+------+-------+--------+
10 rows in set (0.01 sec)

mysql> SELECT * FROM tablename WHERE status = 1 LIMIT 1000,10;
+------+-------+--------+
| id   | value | status |
+------+-------+--------+
| 1935 |    12 |      1 |
| 1939 |    59 |      1 |
| 1940 |    56 |      1 |
| 1941 |    21 |      1 |
| 1942 |     5 |      1 |
| 1943 |    68 |      1 |
| 1944 |    65 |      1 |
| 1947 |    27 |      1 |
| 1948 |    44 |      1 |
| 1950 |    75 |      1 |
+------+-------+--------+
10 rows in set (0.00 sec)

You can't rely on this default ordering, but MySQL can.