In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.
The server creates temporary tables under conditions such as these:
Evaluation of some views, such those that use the
UNION, or aggregation.
Evaluation of derived tables (see Section 220.127.116.11, “Derived Tables”).
Tables created for subquery or semi-join materialization (see Section 8.2.2, “Optimizing Subqueries, Derived Tables, and Views”).
Evaluation of statements that contain an
ORDER BYclause and a different
GROUP BYclause, or for which the
GROUP BYcontains columns from tables other than the first table in the join queue.
ORDER BYmay require a temporary table.
For queries that use the
SQL_SMALL_RESULTmodifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
INSERT ... SELECTstatements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the
SELECT, then inserts those rows into the target table. See Section 18.104.22.168, “INSERT ... SELECT Syntax”.
Evaluation of multiple-table
To determine whether a statement 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.
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 query conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
Presence of a
TEXTcolumn in the table. This includes user-defined variables having a string value because they are treated as
TEXTcolumns, depending on whether their value is a binary or nonbinary string, respectively.
Presence of any string column in a
DISTINCTclause larger than 512 bytes for binary strings or 512 characters for nonbinary strings.
An internal temporary table can be held in memory and
processed by the
MEMORY storage engine, or
stored on disk and processed by the
If an internal temporary table is created 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 defined by the
whichever is smaller. This differs from
MEMORY tables explicitly created with
CREATE TABLE. For such tables,
variable determines how large a table can grow, and there is
no conversion to on-disk format.
In-memory temporary tables are managed by the
MEMORY storage engine, which uses
fixed-length row format.
VARBINARY column values are padded to the
maximum column length, in effect storing them as
On-disk temporary tables are managed by the
MyISAM storage engine using dynamic-width
row format. Columns take only as much storage as needed, which
reduces disk I/O and space requirements, and processing time
compared to on-disk tables that use fixed-length rows.
For statements that initially create an internal temporary
table in memory, then convert it to an on-disk table, better
performance might be achieved by skipping the conversion step
and creating the table on disk to begin with. The
big_tables variable can be
used to force disk storage of internal temporary tables.