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 sizeShort answer
Error 1038 means a sort needed more memory than sort_buffer_size allows. First run EXPLAIN and look for Using filesort. Then add an index that matches the ORDER BY, sort fewer or shorter values, or raise sort_buffer_size for that one session instead of the whole server.
Fix it fast
1Find out which cause you have
Run EXPLAIN on the failing query. If the Extra column says Using filesort, the sort is done in the sort buffer and the causes below apply. If it does not, the index already does the ordering.
EXPLAIN SELECT * FROM orders ORDER BY customer_id, created_at; SHOW VARIABLES LIKE 'sort_buffer_size';2Match what you see, then jump to its fix
- 1
EXPLAIN shows Using filesort in the Extra columnThe ORDER BY has no matching index, so every row goes through the sort bufferFix - 2
Using filesort in EXPLAIN, no useful index possible, and the error appears only on large result setsThe sort buffer is smaller than the sort needsFix - 3
The ORDER BY, or the columns selected next to it, include long VARCHAR, TEXT or similar values, and increasing max_sort_length changed the behaviourLong string values make each sorted row largeFix
- 1
What does the error look like?
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size
Cause 1 of 3
The ORDER BY has no matching index, so every row goes through the sort buffer
1Check
MySQL can skip the sort when an index covers the ORDER BY columns in the same order. Compare the columns you sort by with the indexes on the table.
SHOW INDEX FROM orders;
EXPLAIN SELECT id, customer_id, created_at FROM orders ORDER BY customer_id, created_at;2Fix
Add an index on the sorted columns, in the same order as the ORDER BY. A WHERE on the first column plus an ORDER BY on the next one can also use it.
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);3Verify
Run EXPLAIN again. Using filesort should be gone from Extra.
EXPLAIN SELECT id, customer_id, created_at FROM orders ORDER BY customer_id, created_at;Confirmed by dev.mysql.com
Cause 2 of 3
The sort buffer is smaller than the sort needs
1Check
Read the current value. The default is 262144 bytes (256 KB). Each session that sorts allocates a buffer of this size.
SHOW VARIABLES LIKE 'sort_buffer_size';2Fix
Raise it for the current session only, then run the query. The manual warns that a larger global value slows most queries that sort, so do not raise it for the whole server unless you have measured it.
SET SESSION sort_buffer_size = 4 * 1024 * 1024;
-- run the failing query on this same connection3Verify
The query returns rows instead of error 1038.
SELECT @@session.sort_buffer_size;Confirmed by dev.mysql.com
Cause 3 of 3
Long string values make each sorted row large
1Check
The manual says that raising max_sort_length makes each tuple in the sort buffer larger, which can then require a larger sort_buffer_size. Check the current value.
SHOW VARIABLES LIKE 'max_sort_length';2Fix
Sort on a short key (an id or a date) rather than a long text column, select only the columns you need, and only then raise sort_buffer_size for the session.
SELECT id, title FROM articles ORDER BY published_at DESC LIMIT 50;3Verify
EXPLAIN shows the sort on the short column, and the query completes.
EXPLAIN SELECT id, title FROM articles ORDER BY published_at DESC LIMIT 50;Confirmed by dev.mysql.com
How do I stop it coming back?
- Index the columns you sort by, in the same order as the ORDER BY, and re-check with EXPLAIN after schema changes.
- Select only the columns the page needs and add a LIMIT, so each sort handles fewer and smaller rows.
- Raise sort_buffer_size per session for known heavy reports, not globally.
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
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 fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix