MySQL error 1038 can occur even for a tiny result when each sort record contains a very wide selected value. If ORDER BY cannot use an index, filesort may carry selected columns in memory; one large TEXT, BLOB, or JSON value can then exhaust the session sort buffer.
Last updated: October 2, 2026.
EXPLAIN FORMAT=TREE
SELECT id, payload, created_at
FROM documents
WHERE account_id = 42
ORDER BY created_at DESC
LIMIT 100;
SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
SHOW SESSION STATUS LIKE 'Sort_merge_passes';Look for a sort in the plan and compare the query with a version that selects only the ID and sort key. If the narrow version succeeds, row width—not the number of returned rows—is the immediate clue.
Reduce what filesort must carry
The strongest fix is usually an index that matches the filtering and ordering pattern, such as (account_id, created_at). When the query must sort, rank narrow values first and fetch the wide payload afterward:
SELECT d.*
FROM (
SELECT id
FROM documents
WHERE account_id = 42
ORDER BY created_at DESC
LIMIT 100
) AS ranked
JOIN documents AS d USING (id)
ORDER BY d.created_at DESC;MySQL’s ORDER BY optimization guide describes filesort modes, incremental buffer allocation, and optimizer trace fields that report peak memory.
Increase memory only after fixing the query shape
A larger sort_buffer_size can be tested for the affected session, but a large global value multiplies across concurrent sorts. Do not treat it as a substitute for selecting fewer columns or building the correct index. MySQL documents session-scoped changes in Using system variables. Recheck the plan and workload after every change.
Use MySQL index selection to design the access path, connection diagnostics when memory pressure coincides with concurrency, and the MySQL version check before relying on version-specific optimizer behavior.
