Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A MySQL query becomes very slow as soon as it has an ORDER BY

ErrorUsing filesort

Short answer

A slow ORDER BY nearly always means MySQL sorts the rows itself instead of reading them from an index in order. Run EXPLAIN and look for Using filesort in the Extra column. Then add an index that matches the filter and the sort, remove what stops the index being used, or keep the sort from spilling to disk.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run EXPLAIN on the slow query. If the Extra column says Using filesort, no index is used for the sort and the server sorts the rows itself.

    EXPLAIN SELECT id, title, created_at
    FROM articles
    WHERE author_id = 42
    ORDER BY created_at DESC
    LIMIT 20;
  2. 2Match what you see, then jump to its fix

    1. 1Extra shows Using filesort and SHOW INDEX lists no index that starts with the sort columnNo index exists on the column you sort byFix
    2. 2Using filesort appears although an index on the sort column exists, and the query also filters on a different columnThe WHERE clause uses one index and the ORDER BY needs anotherFix
    3. 3An index on the sort column exists and EXPLAIN still reports Using filesortThe sort has a shape that an index cannot serveFix
    4. 4Sort_merge_passes grows between two runs of the queryThe filesort does not fit in memory and spills to diskFix

What does the error look like?

  • Using filesort
  • The Extra column of EXPLAIN output contains Using filesort
  • The Sort_merge_passes status variable increases while the query runs

Cause 1 of 4

No index exists on the column you sort by

1Check

Without an index, MySQL reads the rows and sorts them with a filesort. The manual says an index can be used for ORDER BY so that the extra sorting is avoided, and that EXPLAIN shows Using filesort when it is not.

SHOW INDEX FROM articles;
EXPLAIN SELECT id, title FROM articles ORDER BY created_at DESC LIMIT 20;

2Fix

Create an index on the sorted column. A query that sorts in one direction can use it even when the query says DESC, because the index need not have the same actual direction, only the same kind of order.

CREATE INDEX idx_articles_created ON articles (created_at);

3Verify

Run EXPLAIN again. Using filesort is gone from the Extra column.

EXPLAIN SELECT id, title FROM articles ORDER BY created_at DESC LIMIT 20;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 4

The WHERE clause uses one index and the ORDER BY needs another

1Check

Rows are fetched through the index that the WHERE clause uses. The manual lists this as a case where the sort cannot use an index: the index that fetches the rows differs from the index in the ORDER BY. An index on the sort column alone does not help.

EXPLAIN SELECT id, title FROM articles
WHERE author_id = 42 ORDER BY created_at;

2Fix

Build one index that starts with the filter column and continues with the sort column. For an equality filter on the first part, the manual shows the index on (key_part1, key_part2) serving ORDER BY key_part2.

CREATE INDEX idx_articles_author_created ON articles (author_id, created_at);

3Verify

EXPLAIN no longer shows Using filesort.

EXPLAIN SELECT id, title FROM articles
WHERE author_id = 42 ORDER BY created_at;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 4

The sort has a shape that an index cannot serve

1Check

The manual names several shapes that block the index: an expression in the ORDER BY such as ORDER BY ABS(col), an index on only a prefix of the column, ORDER BY and GROUP BY with different expressions, and a select-list alias that has the same name as the column. A mix of ASC and DESC needs an index with the same mix.

SHOW CREATE TABLE articles\G
EXPLAIN SELECT id FROM articles ORDER BY created_at DESC, id ASC LIMIT 20;

2Fix

Sort by the bare column, or rename the alias so it does not hide the column. For mixed directions, create the index with the same directions as the ORDER BY.

-- ORDER BY created_at DESC, id ASC
CREATE INDEX idx_articles_created_id ON articles (created_at DESC, id ASC);

3Verify

EXPLAIN reports no Using filesort for the same query.

EXPLAIN SELECT id FROM articles ORDER BY created_at DESC, id ASC LIMIT 20;

Confirmed by dev.mysql.com

Did this fix it?

Cause 4 of 4

The filesort does not fit in memory and spills to disk

1Check

A filesort uses temporary disk files when the result is too large for its memory buffer, which is limited by sort_buffer_size. The manual says to watch the Sort_merge_passes status counter. Wide rows make it worse, because the sort buffer carries the columns that the query references.

SHOW SESSION STATUS LIKE 'Sort_merge_passes';
-- run the slow query
SHOW SESSION STATUS LIKE 'Sort_merge_passes';

2Fix

Select only the columns you need, so each sort entry is small, and add a LIMIT where the page needs only the first rows: a LIMIT query that sorts one table by a non-indexed column can be sorted in memory without temporary files. If it still spills, raise sort_buffer_size for this session only, and point tmpdir at a disk with free space.

SET SESSION sort_buffer_size = 8388608;
SELECT id, title FROM articles
WHERE author_id = 42 ORDER BY created_at DESC LIMIT 20;

3Verify

Read Sort_merge_passes again: it stays flat while the query runs.

SHOW SESSION STATUS LIKE 'Sort_merge_passes';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Design the index for the whole query: equality filter columns first, then the sort column, in the order the query uses them.
  • Run EXPLAIN on every list page query with ORDER BY and LIMIT before it ships, on data about as large as production.
  • Raise sort_buffer_size per session for known heavy reports, not for the whole server.
  • On MySQL 8.4, GROUP BY no longer sorts its result on its own. Add an explicit ORDER BY where order matters, and index for it.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.