"MySQL" About temporary tables

The official MySQL Introduction

In some cases, the server creates internal temporary tables while processing queries.  Such A table can be held MEMORY in memory and processed by the storage engine, or stored on disk and processed MyISAM by the Storage engine. The server may create a temporary table initially as an in-memory table and then convert it to an on-disk table if it becomes Too large. Users has no direct control over when the server creates an internal temporary table or which storage engine the server U SES to manage it

Temporary tables are created in the following situations

• Union query
• Use the temptable algorithm or a view in a union query
• In a join query, ORDER by or group by uses a column that is not the first table, for example: SELECT * from TableA, TableB order by Tablea.price GROUP by Tableb.name
• Table connection, the column for ORDER by is not in the driver table
• Distinct query and add ORDER BY
• When using the Sql_small_result option in SQL
• Sub-query in from
• A table created when subqueries or semi-join
• Multi-table Update sql
• Group_concat () or COUNT (DISTINCT) evaluation

A disk staging table is created in the following scenarios

• table contains text or BLOB columns
• There are more than 512 characters of the character type column (or more than 512 bytes of binary type column in the GROUP by or dstinct column, whether it is more than 512 bytes before 5.6.15)
• In select, Union, UNION ALL queries, there are columns with a maximum length of more than 512 (512 characters for string types and 512 bytes for binary types)
• Execute SQL commands such as show Columns/fields, describe, and so on, because their execution results are in the BLOB column type

Temporary table-related configuration

Tmp_table_size: Specifies the maximum size of the system-created memory temp table

Max_heap_table_size: Specifies the maximum size of a user-created memory table

The maximum size for in-memory temporary tables are the minimum of the tmp_table_size and max_heap_table_size values

Optimization recommendations

Generally avoid using temporary tables

1. Create an index on the column of order by or GROUP by

2. TEXT, blob, and other large print segments, split table

Starting with 5.7.5, add a system option Internal_tmp_disk_storage_engine the engine type that defines the disk staging table is InnoDB, and until then, only MyISAM can be used.

The new system option Default_tmp_storage_engine after 5.6.3 is the engine type that controls the temporary table created by the Create temporary table, which is memory by default and does not confuse the two

Ysql> Show variables like ' default_tmp% '; +----------------------------+--------+| variable_name | Value |+----------------------------+--------+| Default_tmp_storage_engine | InnoDB |+----------------------------+--------+1 row in Set (0.00 sec) mysql> Create temporary table TMP1 (ID int. not NUL  L); Query OK, 0 rows affected (0.17 sec) mysql> Show create TABLE Tmp1 \g*************************** 1. Row *************************** table:tmp1create table:create temporary Table ' tmp1 ' (' id ' int (one) not NULL) ENG Ine=innodb DEFAULT charset=utf81 Row in Set (0.00 sec) mysql> Set default_tmp_storage_engine = MyISAM; Query OK, 0 rows Affected (0.00 sec) mysql> Create temporary table TMP2 (id int (one) unsigned not null comment ' primary k EY '); Query OK, 0 rows affected (0.01 sec) mysql> Show create TABLE TMP2 \g*************************** 1. Row *************************** table:tmp2create table:create temporary Table ' tmp2 ' (' id ' int ') unsigned notNULL COMMENT ' primary key ') Engine=myisam DEFAULT charset=utf81 row in Set (0.00 sec) mysql> Set Default_tmp_storage_eng ine = memory; Query OK, 0 rows Affected (0.00 sec) mysql> Create temporary table Tmp3 (id int (one) unsigned not null comment ' primary k EY '); Query OK, 0 rows Affected (0.00 sec) mysql> Show create TABLE Tmp3 \g*************************** 1. Row *************************** table:tmp3create table:create temporary Table ' Tmp3 ' (' id ' int ') unsigned not NULL COMMENT ' primary key ') Engine=memory DEFAULT Charset=utf8

Reference: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

"MySQL" About temporary tables