GROUP BY, DISTINCT and UNION often need an internal temporary table (Using temporary, The Extra Column). It starts in the in-memory TempTable engine and moves to InnoDB on disk when it outgrows tmp_table_size (default 16 MB, per table) or TempTable as a whole passes temptable_max_ram (3% of RAM within 1-4 GB; 1 GB here). max_heap_table_size limits only the older MEMORY engine. A sort that no index provides (Using filesort) fills sort_buffer_size (256 KB), then merges sorted runs from disk (Sort_merge_passes).
Two page_views queries each ran in a fresh session, read back from performance_schema.session_status: a GROUP BY referrer producing 3 million groups, and an ORDER BY viewed_at, id LIMIT 1 OFFSET 2000000:
| Query | Session setting | Time | Disk temp tables | Merge passes |
|---|---|---|---|---|
| GROUP BY | tmp_table_size 16M | 107.61 s | 1 | 0 |
| GROUP BY | tmp_table_size 1G | 8.64 s | 0 | 0 |
| ORDER BY | sort_buffer_size 256K | 5.44 s | 0 | 458 |
| ORDER BY | sort_buffer_size 64M | 6.27 s | 0 | 1 |
The aggregate ran 12 times slower once its temporary table went to disk. The sort ran faster with the small buffer, despite 458 merge passes; the manual warns that Linux allocation slows above 256 KB and 2 MB. Watch Created_tmp_disk_tables against Created_tmp_tables, fix the query or index first (Composite Indexes), and raise a limit only per statement, with a hint such as /*+ SET_VAR(tmp_table_size = 1073741824) */.