In some cases, the server creates internal temporary tables
while processing queries. A 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. Temporary tables can
be created under conditions such as these:
If there is an ORDER BY
clause and a
different GROUP BY
clause, or if the
ORDER BY
or GROUP BY
contains columns from tables other than the first table in
the join queue, a temporary table is created.
If you use the SQL_SMALL_RESULT
option,
MySQL uses an in-memory temporary table.
DISTINCT
combined with ORDER
BY
may require a temporary table.
To tell whether a query requires a temporary table, use
EXPLAIN
and check the
Extra
column to see whether it says
Using temporary
. See
Section 7.2.1, “Optimizing Queries with EXPLAIN
”.
Some conditions prevent the use of a MEMORY
temporary table, in which case the server uses a
MyISAM
table instead:
A temporary table that is created initially as a
MEMORY
table might be converted to a
MyISAM
table and stored on disk if it becomes
too large. The
max_heap_table_size
system
variable determines how large MEMORY
tables
are allowed to grow. It applies to all MEMORY
tables, including those created with CREATE
TABLE
. However, for internal MEMORY
tables, the actual maximum size is determined by
max_heap_table_size
in
combination with
tmp_table_size
: Whichever value
is smaller is the one that applies. If the size of an internal
MEMORY
table exceeds the limit, MySQL
automatically converts it to an on-disk
MyISAM
table.
User Comments
MySQL also uses temporary tables when processing subqueries in the FROM clause (derived tables), some UNION queries, and some VIEW queries.
"Presence of a BLOB or TEXT column in the table" alone does not prevent the use of an in-memory temporary table unless the BLOB or TEXT column is in the select list and the query uses a group by or distinct clause.
Add your own comment.