Parameters (internal temporary tables, MySQL 8.4 and MariaDB 11.4)
About this exampleAbout 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_sizealone. The MEMORY engine and MariaDB use the smaller oftmp_table_sizeandmax_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".