MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 UNION
statements.
Evaluation of some views, such those that use the
TEMPTABLE
algorithm,
UNION
, or aggregation.
Evaluation of derived tables (see Section 13.2.10.8, “Derived Tables”).
Tables created for subquery or semijoin materialization (see Section 8.2.2, “Optimizing Subqueries and Derived Tables”).
Evaluation of statements that contain an ORDER
BY
clause and a different GROUP
BY
clause, or for which the ORDER
BY
or GROUP BY
contains columns
from tables other than the first table in the join queue.
Evaluation of DISTINCT
combined with
ORDER BY
may require a temporary table.
For queries that use the SQL_SMALL_RESULT
modifier, MySQL uses an in-memory temporary table, unless
the query also contains elements (described later) that
require on-disk storage.
To evaluate
INSERT ...
SELECT
statements 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 13.2.5.1, “INSERT ... SELECT Statement”.
Evaluation of multiple-table
UPDATE
statements.
Evaluation of GROUP_CONCAT()
or COUNT(DISTINCT)
expressions.
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”). EXPLAIN
does not necessarily say Using temporary
for
derived or materialized temporary tables.
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 BLOB
or
TEXT
column in the table.
This includes user-defined variables having a string value
because they are treated as
BLOB
or
TEXT
columns, depending on
whether their value is a binary or nonbinary string,
respectively.
Presence of any string column in a GROUP
BY
or DISTINCT
clause larger
than 512 bytes for binary strings or 512 characters for
nonbinary strings.
Presence of any string column with a maximum length larger
than 512 (bytes for binary strings, characters for nonbinary
strings) in the SELECT
list,
if UNION
or
UNION ALL
is used.
The SHOW COLUMNS
and
DESCRIBE
statements use
BLOB
as the type for some columns, thus
the temporary table used for the results is an on-disk
table.
An internal temporary table can be held in memory and
processed by the MEMORY
storage engine, or
stored on disk and processed by the MyISAM
storage engine.
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 of an in-memory
temporary table is defined by the
tmp_table_size
or
max_heap_table_size
value,
whichever is smaller. This differs from
MEMORY
tables explicitly created with
CREATE TABLE
. For such tables,
only the max_heap_table_size
variable determines how large a table can grow, and there is
no conversion to on-disk format.
When an internal temporary table is created in memory or on
disk, the server increments the
Created_tmp_tables
value.
When an internal temporary table is created on disk, the
server increments the
Created_tmp_disk_tables
value. If too many internal temporary tables are created on
disk, consider increasing the
tmp_table_size
and
max_heap_table_size
settings.
In-memory temporary tables are managed by the
MEMORY
storage engine, which uses
fixed-length row format. VARCHAR
and
VARBINARY
column values are padded to the
maximum column length, in effect storing them as
CHAR
and BINARY
columns.
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.