When Does MySQL Use Internal Temporary Tables? Analysis of UNION, GROUP BY and Other Scenarios

This article explains the circumstances under which MySQL creates internal temporary tables, illustrating the behavior with UNION, UNION ALL, GROUP BY, various hints, and additional query patterns, and provides practical examples and execution plans to help developers understand and optimise their queries.

GROUP BYSQL HintUNION

0 likes · 13 min read

When Does MySQL Use Internal Temporary Tables? Analysis of UNION, GROUP BY and Other Scenarios

Architect

Jul 1, 2016 · Databases

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.

SQL Optimizationinternal temporary tablemysql

0 likes · 10 min read

Two Types of Temporary Tables in MySQL and Their Usage