When are temporary tables used? Summary of the use of MySQL temporary tables

Summary: MySQL uses temporary tables in many cases. Let’s summarize when temporary tables are used. What is a temporary table? MySQL is used to store some intermediate result sets. Temporary tables are only used during the current connection. It can be seen that Mysql will automatically delete the table and release all space when the connection is closed.

MySQL uses temporary tables in many situations. Let’s summarize when temporary tables are used:

What is a temporary table: MySQL is used to store some intermediate result sets. Tables and temporary tables are only visible in the current connection. When the connection is closed, Mysql will automatically delete the table and release all space. Why temporary tables are generated: Generally, temporary tables are created in large numbers due to complex SQL

Temporary tables are divided into two types, one is a memory temporary table and the other is a disk temporary table. The memory temporary table uses the memory storage engine, and the disk temporary table uses the myisam storage engine (disk temporary tables can also use the innodb storage engine. Use the internal_tmp_disk_storage_engine parameter to control which storage engine is used. From mysql5.7.6 onwards, the default is innodb storage. Engine, previous versions defaulted to myisam storage engine). Use the Created_tmp_disk_tables and Created_tmp_tables parameters to view how many disk temporary tables are generated and all generated temporary tables (memory and disk).

The size of the memory temporary table space is controlled by two parameters: tmp_table_size and max_heap_table_size. Generally speaking, the smaller of the two parameters is used to control the maximum value of the temporary table space in memory. For temporary tables that are initially created in memory and later transferred to temporary tables on disk due to too large data, only max_heap_table_size is used. Parameter control. There is no size control for temporary tables generated directly on disk.

The following operations will use temporary tables:

1ã Union query

2ã For view operations, such as using some TEMPTABLE algorithms, union or aggregation

3ã Subquery

4ã semi-join including not in, exist, etc.

5ã Derived table generated by query

6ã Complex group by and order by

7ã Insert select the same table, mysql will generate a temporary table to cache the selected rows

8ã Multiple table updates

9ã GROUP_CONCAT() or COUNT(DISTINCT) statement

. . .

Mysql will also prevent the use of memory table space and use disk temporary tables directly:

1ã The table contains BLOB or TEXT columns

2ã When using union or union all, The select clause has columns larger than 512 bytes

3ã When Show columns or desc tables have LOB or TEXT

4ã GROUP BY or DISTINCT clauses that contain columns longer than 512 bytes Column

Related recommendations:

Using temporary tables in mysql

##How to use temporary tables in Mysql

The above is the detailed content of When are temporary tables used? Summary of the use of MySQL temporary tables. For more information, please follow other related articles on the PHP Chinese website!