Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A query with ORDER BY stops with error 1038, out of sort memory

ErrorERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size

Short 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.

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

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1EXPLAIN shows Using filesort in the Extra columnThe ORDER BY has no matching index, so every row goes through the sort bufferFix
    2. 2Using 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. 3The 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

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

Did this fix it?

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 connection

3Verify

The query returns rows instead of error 1038.

SELECT @@session.sort_buffer_size;

Confirmed by dev.mysql.com

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

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