Fix MySQL ERROR 1038: Out of Sort Memory When Using ORDER BY

TL;DR Quick Fix

MySQL threw ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size because the sort buffer allocated per query is too small. Run this in your MySQL session — no restart needed:

SET SESSION sort_buffer_size = 8 * 1024 * 1024; -- 8 MB

That's it for a quick fix. To make it stick across restarts, add the setting to /etc/mysql/mysql.conf.d/mysqld.cnf (or /etc/my.cnf depending on your distro) and restart MySQL.

What Causes This Error

Every ORDER BY query in MySQL needs temporary memory to sort rows. That memory comes from sort_buffer_size, which defaults to 256 KB — a value set in the mid-2000s when result sets were much smaller and nobody batted an eye at it.

Three common triggers:

• Sorting hundreds of thousands of rows without a covering index
• Many concurrent connections each running sort-heavy queries — each connection gets its own sort buffer, so memory usage multiplies fast
• Using GROUP BY, DISTINCT, or UNION — all of these quietly tap the sort buffer internally

Check your current setting:

SHOW VARIABLES LIKE 'sort_buffer_size';

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+

262144 bytes = 256 KB. That default hasn't aged well for modern workloads.

Fix 1: Increase sort_buffer_size for Your Session

Need a quick fix without touching server config? Set it at the session level:

-- Applies to this connection only
SET SESSION sort_buffer_size = 8 * 1024 * 1024;

-- Now run your original query
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000;

This works well in migration scripts or one-off jobs where restarting MySQL isn't an option.

Fix 2: Make It Permanent in my.cnf

Locate your MySQL config file. The path varies by OS:

• Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf
• CentOS/RHEL: /etc/my.cnf or /etc/mysql/my.cnf
• Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Add or update the value under [mysqld]:

[mysqld]
sort_buffer_size = 4M

Then restart:

# Ubuntu/Debian
sudo systemctl restart mysql

# CentOS/RHEL
sudo systemctl restart mysqld

How much should you set?

It depends on your workload, but this is a reasonable starting point:

• 4 MB: Safe default for most apps
• 8–16 MB: Reporting-heavy databases with large sort operations
• Cap at 64 MB globally — this setting is per-connection, not shared. 100 connections × 64 MB = 6.4 GB RAM just for sort buffers. That adds up fast.

For the occasional monster query, bump it per session instead of globally:

SET SESSION sort_buffer_size = 32 * 1024 * 1024; -- bump up for this query
SELECT ... ORDER BY ... ;
SET SESSION sort_buffer_size = 4 * 1024 * 1024;  -- reset afterward

Fix 3: Add an Index (The Right Long-Term Fix)

Increasing the sort buffer is a band-aid. The actual fix — when possible — is indexing the column you're sorting by. With the right index, MySQL reads rows in sorted order without touching the sort buffer at all.

Say your slow query looks like this:

SELECT id, name, created_at FROM users ORDER BY created_at DESC LIMIT 50;

Add an index:

ALTER TABLE users ADD INDEX idx_created_at (created_at);

Then confirm MySQL actually uses it:

EXPLAIN SELECT id, name, created_at FROM users ORDER BY created_at DESC LIMIT 50;

Check the Extra column. You want it blank or showing Using index. If it still says Using filesort, MySQL is doing an in-memory sort anyway — usually because the query fetches too many columns or hits complex conditions the index can't cover.

Fix 4: Sort Fewer Rows

Sometimes the query itself is the culprit. Sorting a 2-million-row full table scan will exhaust your sort buffer regardless of how generous it is.

Instead of:

SELECT * FROM events ORDER BY event_time DESC;

Filter first, then sort:

SELECT * FROM events
WHERE event_time >= NOW() - INTERVAL 7 DAY
ORDER BY event_time DESC
LIMIT 100;

Pair this with an index on event_time and the sort buffer issue often disappears entirely.

Verify the Fix Worked

Confirm sort_buffer_size is updated:

SHOW VARIABLES LIKE 'sort_buffer_size';

Re-run your original query — ERROR 1038 should be gone. To keep an eye on sort activity server-wide:

SHOW STATUS LIKE 'Sort%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 5     |
| Sort_rows         | 12430 |
| Sort_scan         | 3     |
+-------------------+-------+

Watch Sort_merge_passes. Zero is ideal. If that number keeps climbing, MySQL is repeatedly spilling sort data to disk — your buffer is still too small, or the query needs rethinking.

Summary

• Quick session fix: SET SESSION sort_buffer_size = 8 * 1024 * 1024;
• Permanent fix: set sort_buffer_size = 4M in my.cnf under [mysqld]
• Don't set the global value too high — it's per-connection, not shared memory
• Index the ORDER BY column to avoid the sort buffer entirely
• Monitor Sort_merge_passes — if it's non-zero and rising, your buffer is still undersized