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 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_RESULToption, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
Evaluation of multiple-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
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 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.
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
DISTINCTclause larger than 512 bytes
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