MySQL Filesort: Sorting, Indexes and How to Avoid It

Sorting a lot of rows takes memory and time. If an index already stores the data in the order you need, MySQL can read it in that order and skip the sort.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 20000;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(10) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL
);
INSERT INTO orders (customer_id, status, total, created_at)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT 1 + MOD(i * 7, 200), ELT(1 + MOD(i, 3), 'paid', 'pending', 'shipped'), 10 + MOD(i * 13, 990), '2026-01-01 00:00:00' + INTERVAL i MINUTE FROM n;
ANALYZE TABLE orders;

No index: a filesort

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT id, total FROM orders ORDER BY total DESC LIMIT 5;

Output

OUTPUT

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9720 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set

Extra: Using filesort means MySQL had to sort the rows itself. (The name is misleading: it does not always use a file. It sorts in memory when the data is small enough, and spills to disk when it is not.) With no WHERE, it must read all the rows first.

Add an index in the same order

query.sql

SQL

1
2

CREATE INDEX idx_total ON orders (total);
EXPLAIN FORMAT=TRADITIONAL SELECT id, total FROM orders ORDER BY total DESC LIMIT 5;

Output

OUTPUT

+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | orders | NULL       | index | NULL          | idx_total | 5       | NULL |    5 |   100.00 | Backward index scan; Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------+
1 row in set

Now Using filesort is gone, and the number of rows read is just the LIMIT. The index can be read from either end, so it serves ASC and DESC.

Filter and sort: a composite index

A very common shape: filter by one column, sort by another.

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT id FROM orders WHERE customer_id = 17 ORDER BY created_at DESC LIMIT 5;

Output

OUTPUT

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9720 |    10.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set

With no good index, MySQL filters the rows and sorts them. Create an index with the filter column first, then the sort column:

query.sql

SQL

1
2

CREATE INDEX idx_customer_created ON orders (customer_id, created_at);
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM orders WHERE customer_id = 17 ORDER BY created_at DESC LIMIT 5;

Output

OUTPUT

+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+----------------------------------+
| id | select_type | table  | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                            |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+----------------------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_customer_created | idx_customer_created | 4       | const |   50 |   100.00 | Backward index scan; Using index |
+----+-------------+--------+------------+------+----------------------+----------------------+---------+-------+------+----------+----------------------------------+
1 row in set

All the entries for customer_id = 17 are stored together, already sorted by created_at. MySQL goes to the end of that block and reads backwards 5 rows. No sort at all. This is the most useful sorting index pattern (Composite Indexes).

The rules

The index columns must be in the same order as the ORDER BY, after any columns tested with = in the WHERE.
A range condition (>, <, BETWEEN) on a column stops later columns from being used for ordering. WHERE a > 5 ORDER BY b cannot use an index on (a, b) to sort.
ORDER BY a ASC, b DESC with an index (a, b) could not avoid a sort in old MySQL. MySQL 8.0 supports descending indexes: create INDEX (a ASC, b DESC) and it can.
Mixed functions or expressions in ORDER BY prevent index use (unless there is a functional index).

query.sql

SQL

1
2

CREATE INDEX idx_status_total_desc ON orders (status, total DESC);
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM orders WHERE status = 'paid' ORDER BY total DESC LIMIT 3;

Output

OUTPUT

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_status_total_desc | idx_status_total_desc | 42      | const | 3333 |   100.00 | Using index |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
1 row in set

When a filesort is fine

Small result sets. Sorting 200 rows is nothing.
A query that runs rarely (a nightly report).
When the cost of an extra index (slower writes, more disk) is larger than the benefit.

Do not add an index for every ORDER BY. Look for sorts that happen often on big result sets, often visible in the slow query log (Slow Query Log).

Check the sort buffer

For large sorts, MySQL uses sort_buffer_size per session, and writes to temporary files when it is not enough. If you see a lot of Sort_merge_passes in SHOW STATUS, a bigger buffer or a better index may help. Do not raise the buffer globally to a huge value, because it is allocated per connection.

lightbulb

LIMIT makes indexes pay off

ORDER BY ... LIMIT 10 with a matching index reads 10 rows. Without the index it sorts the whole table to return 10. The biggest wins are on large tables with small limits.