A MySQL query becomes very slow as soon as it has an ORDER BY
Using filesortShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
Extra shows Using filesort and SHOW INDEX lists no index that starts with the sort columnNo index exists on the column you sort byFix - 2
Using 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
An index on the sort column exists and EXPLAIN still reports Using filesortThe sort has a shape that an index cannot serveFix - 4
Sort_merge_passes grows between two runs of the queryThe filesort does not fit in memory and spills to diskFix
- 1
What does the error look like?
Using filesortThe Extra column of EXPLAIN output contains Using filesortThe 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
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
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
A query with ORDER BY stops with error 1038, out of sort memory
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size
Error 1038 means a sort needed more memory than sort_buffer_size allows.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix