Temporary tables — MEMORY to on-disk conversionMySQL 8.4

Watch a GROUP BY fill a MEMORY temp table, hit the limit, and convert to on-disk InnoDB. That cliff is why your query suddenly slows down.

Lesson familyTemp Operator

Parameters (MySQL 8.4 internal temporary tables)

Total rows inserted into the internal temp table.
Size of each materialized row in the temp table.
Default: 16 MiB. MySQL uses min(tmp_table_size, max_heap_table_size).
Default: 16 MiB. The effective limit is min(tmp_table_size, max_heap_table_size).

Example query this animation executes

-- GROUP BY without index → internal temp table
SELECT   department, COUNT(*) AS headcount
FROM     employees
GROUP BY department
HAVING   COUNT(*) > 5;

Internal temp-table operator flow: materialize rows in MEMORY first, then convert to on-disk InnoDB when the effective limit is exceeded.

What you'll see in the animation

  • Rows from child operators are inserted into internal MEMORY temp table first.
  • Capacity is bounded by min(tmp_table_size, max_heap_table_size).
  • Once limit is crossed, MySQL converts to on-disk InnoDB temp table.
  • Remaining inserts hit disk-backed structure, increasing latency.
  • Higher effective limit delays conversion and reduces disk I/O risk.
Ready — press Play
0.0s 0.0s

Cost readout (internal temporary tables)

Effective limit ?MySQL uses min(tmp_table_size, max_heap_table_size) as the effective MEMORY temp table limit.

—

MEMORY capacity (rows) ?How many rows fit in the MEMORY temp table before the on-disk conversion is triggered.

—

Rows in MEMORY ?Rows inserted while the temp table is still in-memory. These are fast, no disk I/O.

—

Rows on disk ?Rows inserted after the MEMORY limit was exceeded and the table was converted to on-disk InnoDB.

—

Disk conversion? ?If Yes, the MEMORY temp table exceeded the limit and was converted to on-disk InnoDB — a costly operation.

—

Learn more — when does MySQL use internal temp tables?

MySQL creates internal temporary tables for several query patterns:

GROUP BY / DISTINCT / ORDER BY with GROUP BY — when there's no covering index that produces rows in the grouped/sorted order. This is the most common trigger.

UNION / UNION DISTINCT — the de-duplication step materializes into a temp table. UNION ALL does not need one.

Derived tables and CTEs — subqueries in FROM clauses and non-recursive CTEs are often materialized.

Semijoin materialization — the optimizer may materialize the inner side of IN (SELECT ...) into a temp table and probe it for each outer row.

The conversion to on-disk happens when any of these are true: (1) data exceeds min(tmp_table_size, max_heap_table_size), (2) the table contains BLOB/TEXT columns (can't be stored in MEMORY engine), (3) the total row length exceeds MEMORY engine limits.

In MySQL 8.4, on-disk temp tables use the TempTable storage engine by default (internal_tmp_mem_storage_engine=TempTable), which is more efficient than the old InnoDB-backed path. The TempTable engine also has its own memory limit: temptable_max_ram (default 1 GiB).

Sources: MySQL 8.4 reference manual §10.4.4 "Internal Temporary Table Use"; MySQL 8.4 reference manual §7.1.8 "Server System Variables".