Understanding MySQL Temporary Table Engines and Their Configuration

MySQL temporary tables are divided into two main categories: the built‑in Memory engine (also known as the temporary table engine) and user‑defined temporary tables.

1. Memory engine – created with the ENGINE=MEMORY option, for example:

create table tmp1(id int, str1 varchar(100)) engine = memory;

The size of these tables is limited by the max_heap_table_size parameter; exceeding it results in an error.

2. User‑defined temporary tables include:

Explicit temporary tables created with CREATE TEMPORARY TABLE , e.g.:

create temporary table tmp2 (id int, str1 varchar(100));

Internal temporary tables generated during query execution (e.g., for UNION, ORDER BY, derived tables, large objects, subqueries, etc.).

Both types can be inspected using the status variable:

show global status like '%tmp_%tables%';

The result shows counters for temporary tables created on disk ( Created_tmp_disk_tables ) and the total number of temporary tables ( Created_tmp_tables ), which are controlled by the smaller of tmp_table_size and max_heap_table_size .

Before MySQL 5.7, internal temporary tables were always MyISAM. Starting with MySQL 5.7, the internal_tmp_mem_storage_engine variable allows choosing between MyISAM and InnoDB, with InnoDB being the preferred option, and these tables are stored in the temporary tablespace ibtmp1 (configurable via innodb_temp_data_file_path ).

MySQL 8.0 introduces a dedicated TempTable engine that solves two major issues of earlier engines: inefficient storage of VARCHAR columns (which were padded to CHAR length) and the default disk storage of large objects (TEXT, BLOB, JSON). The engine is selected via the internal_tmp_mem_storage_engine variable, defaulting to TempTable , and its memory usage is limited by temp_table_max_ram (default 1 GB); excess data is spilled to disk in ibtmp1 .

Metrics for the TempTable engine are available in the performance schema table memory_summary_global_by_event_name :

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE event_name LIKE '%temptable%'
\G

Sample output shows counters such as memory/temptable/physical_disk (temporary tables written to disk) and memory/temptable/physical_ram (temporary tables kept in memory).

In summary, MySQL 8.0’s TempTable engine provides a default internal temporary table engine with proper variable‑length character storage, configurable memory limits via temp_table_max_ram , and detailed performance metrics.