MySQL — Sorts Spill to Disk: Creating tmp table to disk

Fix slow ORDER BY and GROUP BY queries whose sorts and temp tables spill to disk, size sort_buffer and tmp_table appropriately, and index the sort order.

intermediatedebuggingmysqlquery-tuningperformance

Symptoms

Slow query log entries flagged with Creating sort index or Copying to tmp table on disk for seconds
EXPLAIN shows Using filesort or Using temporary on queries that were fast on smaller data
Disk IO spikes on the tmpdir filesystem whenever report queries run

Possible causes

ORDER BY columns have no covering index, so every execution sorts the full result set
sort_buffer_size is below the row width times row count, forcing a merge sort through disk files
GROUP BY with temp tables exceeds tmp_table_size / max_heap_table_size and converts to on-disk tables
LIMIT with high offsets forces sorting the entire prefix anyway

Diagnosis steps

Read the execution plan

mysql -e "EXPLAIN FORMAT=JSON SELECT ... ORDER BY created_at DESC LIMIT 100\G" | grep -E "using_filesort|using_temporary_table|sorts"

Interpretation

The JSON plan shows using_filesort, estimated rows to sort, and whether the sort fits memory — the full spill story in one output.

Check the session spill status

mysql -e "SHOW GLOBAL STATUS LIKE 'Sort_merge_passes'; SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';"

Interpretation

Sort_merge_passes above zero means memory sorts are merging through disk; growing Created_tmp_disk_tables tracks temp-table conversions.

Compare row width versus buffers

mysql -e "SELECT AVG(LENGTH(CONCAT_WS(',', col1, col2))) FROM t;"

Interpretation

Average row width times rows to sort versus sort_buffer_size predicts the spill arithmetically.

Root cause

When the rows a query must sort exceed the per-connection sort buffer, MySQL writes runs to disk and merge-sorts them through the tmpdir. Similarly, intermediate GROUP BY tables beyond the memory tmp limits convert to on-disk tables. The query then pays disk IO per execution, and latency scales with data size.

Fix

Add a covering index matching the ORDER BY (and WHERE prefix) so no sort happens at all, project only needed columns to shrink row width, raise sort_buffer_size and tmp_table_size to fit measured working sets, and replace high-offset pagination with keyset pagination.

Risk note

sort_buffer_size is per-connection: a global raise multiplies by max_connections and can exhaust memory. Size from measured sorts, keep sessions bounded.

Verify the fix

After indexing and sizing, EXPLAIN shows no filesort, Sort_merge_passes stays at zero under load, and the slow query log no longer flags the statement.

Symptom walkthrough

The slow log annotates the mechanism directly:

# Query_time: 4.9  Lock_time: 0.000  Rows_sent: 100  Rows_examined: 2,140,882
# Creating sort index
SELECT id, title, body FROM articles ORDER BY created_at DESC LIMIT 100;

Two million rows examined to send one hundred: the sort ran over the entire table because no index carries the order. Confirm with the status counters — Sort_merge_passes is the disk-merge receipt, and Created_tmp_disk_tables tracks GROUP BY conversions:

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

Fix in practice

Index the order so no sort exists:

CREATE INDEX articles_created ON articles (created_at DESC, id);
-- WHERE + ORDER BY sharing the index prefix:
-- SELECT ... WHERE status = 'published' ORDER BY created_at DESC
CREATE INDEX articles_status_created ON articles (status, created_at DESC, id);

Shrink the row width — sort cost scales with bytes per row:

-- deferred join: sort only ids, then join back:
SELECT a.* FROM articles a
JOIN (SELECT id FROM articles ORDER BY created_at DESC LIMIT 100) t
  ON a.id = t.id;

Size memory from arithmetic, not folklore:

sort_buffer_size = 8M        # row_width x rows for your measured sorts
tmp_table_size = 256M        # with max_heap_table_size matched

And replace OFFSET pagination — keyset form sorts nothing extra:

WHERE (created_at, id) < (last_seen_created, last_seen_id)
ORDER BY created_at DESC, id DESC LIMIT 100;

Verification

Run EXPLAIN FORMAT=JSON on the fixed statement and confirm using_filesort is gone from the sort node; the plan should read the index in order. Snapshot the global counters before and after a load run: Sort_merge_passes stays at zero and Created_tmp_disk_tables stops growing during the report workload. Watch the tmpdir filesystem IO during the same run — the disk spikes that correlated with report queries disappear. Re-check the slow query log: the statement no longer carries Creating sort index annotations, and query time drops to single-digit milliseconds. For keyset pagination, verify page two returns instantly with Rows_examined near the LIMIT instead of millions.