Temporary tables: in-memory to on-disk conversionMySQL 8.4 • MariaDB 11.4

Watch a GROUP BY fill an in-memory temp table with one row per group, hit its size limit, and convert to an on-disk table. That cliff is why your query suddenly slows down, and the engine decides where the cliff is.

Lesson familyTemp operator

Parameters (internal temporary tables, MySQL 8.4 and MariaDB 11.4)

About this example
Ready: press Next step, or Play to run it all
0.0s 0.0s

About this example

Example query this animation executes

-- GROUP BY without index → internal temp table
SELECT   customer_id, COUNT(*) AS orders, SUM(total) AS spent
FROM     orders
GROUP BY customer_id;

No index on orders(customer_id), so the server groups the rows in an internal temporary table with one row per customer. The table starts in memory and moves to disk if it outgrows its size limit.

What you'll see in the animation

  • The temp table holds one row per customer_id: an order for a customer it already has only updates that row's COUNT and SUM. Each pill is a new group such as "customer_id=1001", and the 12 pills each stand for one twelfth of the groups you set above.
  • Green box: the in-memory temp table. Its bar fills toward the size limit printed above it on the right.
  • That limit depends on the engine. MySQL's default, TempTable, uses tmp_table_size alone. The MEMORY engine and MariaDB use the smaller of tmp_table_size and max_heap_table_size.
  • At 100%, the table is copied into the red box: an on-disk table (InnoDB on MySQL, Aria on MariaDB). Red pills are rows inserted after that conversion.
  • Try it: set max_heap_table_size below tmp_table_size, then switch the engine and watch the limit move (or not).
  • Next step inserts one batch of groups at a time; the simplified code beside the drawing highlights the lines each step runs.

Cost readout (internal temporary tables)

Effective limit ?The most this one temp table can hold in memory. TempTable (MySQL's default) uses tmp_table_size. The MEMORY engine and MariaDB use the smaller of tmp_table_size and max_heap_table_size.

—

Limit set by ?The setting to raise if you want this table to stay in memory. With MEMORY or MariaDB, raising the larger setting changes nothing: raise the smaller one, or both.

—

In-memory capacity (group rows) ?How many group rows fit under the limit before the conversion to disk starts: the limit divided by the row size.

—

Group rows inserted in memory ?Groups inserted while the temp table is still in memory. These are fast: no disk I/O.

—

Group rows inserted on disk ?Groups inserted after the table outgrew its limit and moved to an on-disk table (InnoDB on MySQL, Aria on MariaDB).

—

Disk conversion? ?Yes means the table outgrew its limit. The server then copies every row already in memory into a new on-disk table, which is slow.

—

Learn more — which limit applies, and when does MySQL use internal temp tables?

Which limit applies. MySQL 8.4 keeps internal temp tables in the TempTable engine by default (internal_tmp_mem_storage_engine=TempTable). Since MySQL 8.0.28, each TempTable table may use up to tmp_table_size; max_heap_table_size does not apply. With internal_tmp_mem_storage_engine=MEMORY, the limit is the smaller of tmp_table_size and max_heap_table_size. MariaDB also uses the smaller of the two (there, tmp_table_size is an alias of tmp_memory_table_size).

The shared TempTable pool. All TempTable tables on the server also share one RAM budget, temptable_max_ram (MySQL 8.4 default: 3% of physical memory, at least 1 GiB and at most 4 GiB; 1 GiB on 8.0 to 8.3 and on 9.6+ unless server_memory is set). Once it is used up, TempTable may continue in memory-mapped files only when temptable_use_mmap=ON (default OFF in 8.4, removed in 9.4) and only up to temptable_max_mmap (default 0). So with default settings a busy server can push a table to disk even while that table is under tmp_table_size.

Where it spills. MySQL rebuilds the table as an on-disk InnoDB temporary table and copies the rows already in memory into it. MariaDB rebuilds it as an on-disk Aria table (MyISAM only in builds compiled with USE_ARIA_FOR_TMP_TABLES=OFF).

Straight to disk. Some tables skip memory entirely. On MySQL that happens with big_tables=ON (unless the query uses SQL_SMALL_RESULT), and, with the MEMORY engine only, for tables with BLOB or TEXT columns. TempTable keeps BLOB and TEXT in memory. MariaDB sends tables with BLOB or TEXT columns straight to Aria.


Queries that create internal temporary tables:

GROUP BY / DISTINCT / ORDER BY with GROUP BY: when no index produces rows in the grouped or 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.

Sources: MySQL 8.4 source sql/sql_tmp_table.cc (row cap per engine, create_ondisk_from_heap), storage/temptable/include/temptable/allocator.h (per-table and shared limits), sql/sys_vars.cc (defaults); MariaDB 11.4 sql/sql_select.cc (in-memory cap, conversion to Aria); MySQL 8.4 reference manual §10.4.4 "Internal Temporary Table Use in MySQL".