MySQL

MySQL "Out of Sort Memory" Error on Large Queries

MySQL's "out of sort memory" error means a query needing to sort a large result set (via ORDER BY, GROUP BY, or certain join operations) exceeded the memory allocated for that sort operation — the fix usually involves both a memory tuning adjustment and, more durably, an index that avoids the expensive sort in the first place.

The Problem

A query involving sorting or grouping a large result set fails with:

ERROR 1038 (HY000): Out of sort memory, consider increasing server sort buffer size

The same type of query might work fine on smaller tables or with more selective filters, only failing once the amount of data being sorted grows past a certain point.

Why It Happens

MySQL allocates a fixed amount of memory (sort_buffer_size) per sort operation, and when that's not enough for the required sort, it falls back to using temporary files on disk — but if even that fallback runs out of usable space or hits certain internal limits, this specific error results. Common contributing factors:

  • The default sort_buffer_size is quite conservative and can be genuinely too small for sorting large result sets, especially with wide rows (many or large columns) being sorted
  • A query lacks an index that could allow MySQL to retrieve rows already in the needed order, forcing an expensive in-memory or on-disk sort operation that a well-placed index would have avoided entirely
  • Sorting an unnecessarily large result set because a query fetches more rows or columns than actually needed before applying the sort, rather than filtering down to the minimum necessary data first
  • Available disk space for temporary files being insufficient on the server, compounding an already large in-memory sort requirement

The Fix

First, check the current sort buffer configuration:

SHOW VARIABLES LIKE 'sort_buffer_size';

If it's set to a small default value, increase it, though be cautious about how broadly you apply this change — sort_buffer_size is allocated per session, and per sort operation within a session, so a large global increase can add up to significant total memory usage under concurrent load:

SET GLOBAL sort_buffer_size = 4194304; -- 4MB, adjust based on your workload

For a more targeted approach, increase the buffer size only for the specific session running the problematic query, rather than changing the global default for every connection:

SET SESSION sort_buffer_size = 8388608; -- 8MB for this session only

The more durable fix, rather than continuing to raise memory allocation as data grows, is adding an index that lets MySQL avoid the expensive sort operation entirely. If a query frequently sorts by a specific column, an index on that column can allow MySQL to retrieve rows already in sorted order directly from the index, skipping the separate sort step altogether:

CREATE INDEX idx_orders_created_at ON orders(created_at);

SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;
-- with the index, this can potentially avoid a filesort entirely

Confirm whether an index is actually being used to avoid the sort by checking the query plan:

EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;

Look at the Extra column in the output — Using filesort confirms MySQL is performing an explicit sort operation (consuming the sort buffer, potentially triggering this error under enough load), while its absence, combined with an index being used, indicates the sort is being avoided via index ordering instead.

Reduce the amount of data being sorted in the first place by filtering as early and as tightly as possible before any sort happens — a WHERE clause that reduces the row count significantly before sorting reduces sort memory pressure proportionally, compared to sorting the entire unfiltered table and only then applying a limit.

Still Not Working?

If increasing sort_buffer_size and adding appropriate indexes both fail to resolve the issue for a genuinely necessary large sort operation, check available disk space for MySQL's temporary directory, since on-disk sort fallback requires adequate temp space, and a server running low on disk space can hit this error even with reasonable buffer settings, for a completely different underlying reason than memory configuration:

SHOW VARIABLES LIKE 'tmpdir';
df -h /path/to/tmpdir

If the temp directory's underlying filesystem is genuinely low on space, freeing space there (or reconfiguring tmpdir to point at a location with more available capacity) addresses a root cause that no amount of sort_buffer_size tuning alone would fix.

You might also like