When Does MySQL Use Temporary Tables and How Does It Choose the Storage Engine?

1. Preparation

Two example tables t_recbuf and t_internal_tmp_table have identical structures. The definition of t_recbuf is shown below.

CREATE TABLE `t_recbuf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `i1` int(10) unsigned DEFAULT '0',
  `str1` varchar(32) DEFAULT '',
  `str2` varchar(255) DEFAULT '',
  `c1` char(11) DEFAULT '',
  `e1` enum('北京','上海','广州','深圳','天津','杭州','成都','重庆','苏州','南京','洽尔滨','沈阳','长春','厦门','福州','南昌','泉州','德清','长沙','武汉') DEFAULT '北京',
  `s1` set('吃','喝','玩','乐','衣','食','住','行','前后','左右','上下','里外','远近','长短','黑白','水星','金星','地球','火星','木星','土星','天王星','海王星','冥王星') DEFAULT '',
  `bit1` bit(8) DEFAULT b'0',
  `bit2` bit(17) DEFAULT b'0',
  `blob1` blob,
  `d1` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8;

2. Scenarios where temporary tables are used

order by and group by columns differ

order by or group by column is not the first table in a join

aggregations with DISTINCT (e.g., count(distinct i1))

queries using UNION or UNION DISTINCT

derived tables (EXPLAIN select_type = DERIVED)

semi‑join materialization

subquery materialization (including non‑correlated subqueries, semi‑join duplicate elimination)

INSERT … SELECT where source and target are the same table

These scenarios are based on the official MySQL documentation with minor adjustments.

To verify whether a query uses a temporary table, check the Extra column of EXPLAIN for the text Using temporary.

3. Which storage engine is used for temporary tables?

MySQL can create temporary tables with MEMORY, MyISAM, or InnoDB engines. By default it uses MEMORY, which stores data and indexes in RAM.

Two conditions cause MySQL to switch to a disk‑based engine:

The temporary table contains a large object (BLOB) column.

The system variable big_tables is set to ON, forcing the use of MyISAM or InnoDB.

If big_tables=ON but the query is known to produce a small temporary table, the SQL_SMALL_RESULT hint can keep the MEMORY engine.

The disk engine is chosen by the variable internal_tmp_disk_storage_engine, whose value can be MyISAM or InnoDB (default InnoDB).

4. When a MEMORY temporary table becomes a disk temporary table

MEMORY tables store fixed‑length rows and cannot hold BLOB columns. Variable‑length columns (VARCHAR, VARBINARY) are stored using their defined maximum length, effectively behaving like CHAR/BINARY.

If the total memory used by the temporary table exceeds the threshold defined by the smaller of tmp_table_size and max_heap_table_size, MySQL automatically converts the table to a disk‑based engine (MyISAM or InnoDB).

5. Which fields are written into a temporary table?

Temporary tables can serve the whole query or a single aggregate function.

Whole‑query temporary tables store every column returned by the storage engine to the server layer. For example, in SELECT e1, COUNT(i1) FROM t_internal_tmp_table GROUP BY e1, the values of e1 and the computed COUNT(i1) are written.

Aggregate‑function temporary tables store only the columns used inside the aggregate, e.g.,

SELECT e1, COUNT(DISTINCT i1) FROM t_internal_tmp_table GROUP BY e1

writes only i1 and creates a unique index on it.

6. Indexes created on temporary tables

For GROUP BY and DISTINCT operations MySQL creates a unique index on the grouping/distinct columns, ensuring one row per group. Only one index can exist per temporary table.

6.1 GROUP BY

Example SQL: SELECT e1, COUNT(i1) FROM t_internal_tmp_table GROUP BY e1. MySQL creates a unique index named <group_key> on e1. The processing steps are:

Read a row from the source table and look up e1 in the temporary table.

If the row exists, update the count and write back.

If the row does not exist, insert a new record with count initialized.

6.2 DISTINCT

For

SELECT e1, COUNT(DISTINCT i1) FROM t_internal_tmp_table GROUP BY e1

, only i1 is stored, and a unique index named <auto_key> (later renamed to <distinct_key>) guarantees uniqueness.

6.3 Hash field fallback

If the number, length, or total size of the grouping/distinct columns exceeds engine limits, MySQL adds a hidden <hash_field> column with a non‑unique index. The hash is computed from all grouping columns, and lookup/insertion follows a multi‑step process to ensure correctness despite possible hash collisions.

7. Statistics of internal temporary tables

Each creation increments the status variable Created_tmp_tables. When a memory table is converted to disk, Created_tmp_disk_tables is incremented. The ratio of these counters indicates the proportion of disk temporary tables.

Two optimization approaches to reduce memory‑to‑disk conversion:

Increase tmp_table_size and max_heap_table_size to allow larger in‑memory tables.

Set big_tables=ON to force disk storage for queries that inevitably need large temporary tables.

8. Summary

The article enumerates common scenarios that trigger temporary tables, explains MySQL’s default MEMORY engine and the conditions that switch to MyISAM or InnoDB, describes the threshold‑based conversion to disk tables, shows how SQL_SMALL_RESULT and SQL_BIG_RESULT hints influence engine choice, details which columns are written for GROUP BY and DISTINCT, and outlines indexing strategies and fallback hash mechanisms. It also provides variables for monitoring temporary‑table usage and practical tuning knobs.