Two Types of Temporary Tables in MySQL and Their Usage
The article explains MySQL's external and internal temporary tables, describes ten scenarios where internal temporary tables are created—including hints, derived tables, ORDER BY, GROUP BY, IN‑subqueries, UNION and aggregates—and provides example SQL statements with EXPLAIN JSON output to illustrate performance impacts.
MySQL Two Types of Temporary Tables
External Temporary Tables
External temporary tables are created with CREATE TEMPORARY TABLE , visible only to the current session, automatically dropped when the session ends, and may share a name with a permanent table, which then becomes invisible to the session.
Internal Temporary Tables
Internal temporary tables are lightweight tables that MySQL creates automatically to store intermediate results during query optimization or execution. They are not visible to users, but their usage can be detected via EXPLAIN or SHOW STATUS . They can be HEAP (memory‑resident) or OnDisk (disk‑based) and may be converted automatically based on size limits such as MAX_HEAP_TABLE_SIZE .
The article lists ten situations where MySQL will employ internal temporary tables, providing example statements and corresponding EXPLAIN FORMAT=JSON output.
Using the SQL_BUFFER_RESULT hint to buffer result sets.
Queries containing derived tables (DERIVED_TABLE) and disabling derived_merge .
Reading from system tables.
DISTINCT that cannot be optimized away.
ORDER BY that cannot be optimized, including cases with BNL/BKA joins, complex expressions, scalar subqueries, or mismatched ORDER BY and GROUP BY columns.
GROUP BY that cannot be optimized, such as BNL/BKA joins, non‑first‑table columns, differing ORDER BY columns, ROLLUP with outer joins, and scalar subqueries.
IN expressions transformed to semi‑joins (materialization or duplicate‑weedout).
UNION operations that require duplicate elimination.
Multi‑table UPDATE statements.
Aggregate functions like COUNT(DISTINCT) and GROUP_CONCAT that trigger temporary tables.
For each case, the article shows the relevant SQL statement, the JSON output from EXPLAIN FORMAT=JSON , and notes whether a temporary table, filesort, or other optimization is used.
To minimize performance impact, the author advises avoiding these patterns when possible, especially for large data sets, because internal temporary tables may spill to disk.
Source: Database Kernel Monthly, 2016‑06‑07.
Architect
Professional architect sharing high‑quality architecture insights. Topics include high‑availability, high‑performance, high‑stability architectures, big data, machine learning, Java, system and distributed architecture, AI, and practical large‑scale architecture case studies. Open to ideas‑driven architects who enjoy sharing and learning.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.