Understanding MySQL Filesort: Algorithms, Memory Allocation, and Temporary File Impact

MySQL uses two primary filesort algorithms—original (row‑id based) and modified (no row‑id)—to handle ORDER BY operations, each with distinct handling of sort fields and additional data (addon fields).

The sort field length is estimated from column definitions (e.g., varchar(300) ≈ 600 bytes for UTF‑8) and capped by the max_sort_length variable, while addon fields may be packed to save space if they are variable‑length.

Memory for the sort buffer is allocated based on the smaller of the estimated number of rows from the InnoDB leaf node and the number of rows that fit into the configured sort_buffer_size , helping to limit memory consumption.

When the sort buffer fills, MySQL performs an in‑memory sort and writes sorted chunks to temporary files (named with the MY prefix). Subsequent merge passes combine these chunks, potentially creating additional temporary files, and finally produce a sorted result that may be smaller after removing sort keys.

Key factors that cause temporary files to grow larger than the original table include large defined column sizes for sort fields, use of the original filesort algorithm (which stores full sort fields plus row IDs), and lack of packing for variable‑length columns.

The article demonstrates these concepts with concrete examples, shows how to inspect internal state using GDB, and explains how to interpret OPTIMIZER_TRACE output to identify the sorting mode, memory usage, and number of temporary files.

Practical recommendations include using appropriate indexes, limiting ORDER BY columns to fixed‑length types when possible, avoiding SELECT * to reduce addon fields, and tuning max_sort_length and sort_buffer_size to mitigate excessive temporary file creation.