8.8.5 How MySQL Uses Internal Temporary Tables

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 MyISAM
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.

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
tmp_table_size and
max_heap_table_size values.
This differs from MEMORY tables explicitly
created with CREATE TABLE: For
such tables, only the
max_heap_table_size system
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
Created_tmp_tables status
variable. If the server creates the table on disk (either
initially or by converting an in-memory table) it increments the
Created_tmp_disk_tables status
variable.

Some conditions prevent the use of an in-memory temporary table,
in which case the server uses an on-disk table instead: