How MySQL Converts Temporary Tables to Disk

This article explains how MySQL manages internal implicit temporary tables, focusing on the transition from memory-based storage to disk-based storage during query execution. You will learn about the triggers that initiate this conversion, the underlying storage engines involved, and how to configure your database to optimize performance and prevent disk I/O bottlenecks.

Why MySQL Creates Implicit Temporary Tables

During complex query executions, the MySQL optimizer often needs to store intermediate result sets. These are known as implicit (or internal) temporary tables. Common SQL clauses that trigger their creation include:

• GROUP BY or ORDER BY clauses on different columns.
• DISTINCT combined with ORDER BY.
• Subqueries or derived tables.
• Evaluation of UNION statements.

By default, MySQL attempts to create these temporary tables in-memory to ensure fast read and write operations.

The In-Memory Stage and Thresholds

Initially, MySQL allocates memory for the temporary table using a specific storage engine.

• MySQL 8.0 (and newer): Uses the TempTable storage engine as the default.
• MySQL 5.7 (and older): Uses the MEMORY storage engine.

The table remains in memory as long as it does not exceed pre-configured size limits. If the size of the temporary table surpasses these limits, MySQL must transition the table to disk. The limits are determined by the following system variables:

• For the TempTable engine: Governed by temptable_max_ram. Once the in-memory allocation for TempTable exceeds this limit, MySQL begins using memory-mapped files or switches to disk.
• For the MEMORY engine: Governed by the lesser of tmp_table_size and max_heap_table_size. If the table size exceeds this limit, conversion to disk is triggered.

Additionally, in older MySQL versions, the presence of BLOB or TEXT columns in the result set would force the immediate creation of an on-disk temporary table, bypassing the in-memory stage entirely. In MySQL 8.0, the TempTable engine can store BLOB and TEXT columns in memory, reducing the need for immediate disk allocation.

How the Conversion to Disk Occurs

When an in-memory temporary table reaches its size threshold, MySQL performs the following steps to convert it to a disk-based table:

1. Engine Selection: MySQL determines which on-disk storage engine to use. In MySQL 8.0, the default on-disk engine for internal temporary tables is InnoDB (configured via the internal_tmp_mem_storage_engine variable). In older versions, it defaults to MyISAM.
2. Table Creation: A new, identical table structure is created on disk.
3. Data Transfer: MySQL copies all the rows currently accumulated in the in-memory table into the newly created disk-based table.
4. In-Memory Freeing: The memory allocated to the original in-memory table is freed.
5. Query Continuation: The query continues execution, writing any remaining intermediate rows directly to the disk-based table.

If the TempTable engine is configured to use memory-mapped files (via temptable_use_mmap=ON), MySQL may bypass standard disk table engines and map memory directly to temporary files on disk when temptable_max_ram is exceeded.

Monitoring and Optimization

Converting temporary tables to disk is resource-intensive and can cause significant query latency due to disk I/O. You can monitor this behavior using the following status variables:

• Created_tmp_tables: The number of internal temporary tables created in memory or on disk.
• Created_tmp_disk_tables: The number of internal temporary tables created directly on disk or converted from memory to disk.

If Created_tmp_disk_tables is high relative to Created_tmp_tables, consider optimizing your queries (e.g., by adding indexes to avoid GROUP BY filesorts) or increasing the values of tmp_table_size, max_heap_table_size, or temptable_max_ram to allow larger datasets to remain in memory.