In some cases, the server creates internal temporary tables
while processing queries. Such a table can be held in memory and
processed by the
MEMORY storage engine, or
stored on disk and processed by the
storage engine. The server may create a temporary table
initially as an in-memory table, then convert it to an on-disk
table if it becomes too large. Users have no direct control over
when the server creates an internal temporary table or which
storage engine the server uses to manage it.
Temporary tables can be created under conditions such as these:
UNION queries use temporary
Some views require temporary tables, such those evaluated
TEMPTABLE algorithm, or that
UNION or aggregation.
If there is an
ORDER BY clause and a
GROUP BY clause, or if the
ORDER BY or
contains columns from tables other than the first table in
the join queue, a temporary table is created.
DISTINCT combined with
BY may require a temporary table.
If you use the
MySQL uses an in-memory temporary table, unless the query
also contains elements (described later) that require
Derived tables (subqueries in the
Tables created for subquery or semi-join materialization.
To determine whether a query requires a temporary table, use
EXPLAIN and check the
Extra column to see whether it says
Using temporary (see
Section 8.8.1, “Optimizing Queries with EXPLAIN”).
will not necessarily say
Using temporary for
derived or materialized temporary tables.
If an internal temporary table is created initially as an
in-memory table but becomes too large, MySQL automatically
converts it to an on-disk table. The maximum size for in-memory
temporary tables is the minimum of the
This differs from
MEMORY tables explicitly
CREATE TABLE: For
such tables, only the
variable determines how large the table is permitted to grow and
there is no conversion to on-disk format.
When the server creates an internal temporary table (either in
memory or on disk), it increments the
variable. If the server creates the table on disk (either
initially or by converting an in-memory table) it increments the
Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
Presence of any string column in a
DISTINCT clause larger
than 512 bytes for binary strings or 512 characters for
nonbinary strings. (Before MySQL 5.6.15, the limit is 512
bytes regardless of string type.)