[Mysql] about temporary table _ MySQL

Mysql official introduction to Insomecases, theservercreatesinternaltemporarytableswhileprocessingqueries. SuchatablecanbeheldinmemoryandprocessedbytheMEMORYstorage... mysql official introduction

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

Temporary tables will be created in the following situations

UNION query

Use the TEMPTABLE algorithm or view in the UNION query

In a JOIN query, order by or group by uses columns other than the first table, for example: SELECT * from TableA, TableB order by TableA. price GROUP by TableB. name

In table join, the order by column is not in the driver table

When order by is added to the DISTINCT query

When the SQL _SMALL_RESULT option is used in SQL

Subquery in FROM

Table created in subquery or semi-join

Multi-table update SQL

GROUP_CONCAT () or COUNT (DISTINCT) evaluation

In the following situations, a temporary disk table is created

The table contains TEXT or BLOB columns.

The group by or DSTINCT column contains more than 512 character type columns (or more than 512 bytes of binary type columns, before 5.6.15, only whether it exceeds 512 bytes)

In SELECT, UNION, and union all queries, a column with a maximum length of more than 512 exists (512 characters for the string type and 512 bytes for the binary type)

Execute SQL commands such as SHOW COLUMNS/FIELDS and DESCRIBE, because their execution results use the BLOB column type.

Temporary table configuration

Tmp_table_size: specifies the maximum size of the memory temporary table created by the system.

Max_heap_table_size: specifies the maximum size of the memory table created by the user.

The maximum size for in-memory temporary tables is the minimum of thetmp_table_size and max_heap_table_size values

Optimization suggestions

Avoid using temporary tables.

1. create an index on the column 'Order by' or 'group'

2. split the table with large fields such as TEXT and BLOB.

Starting from 5.7.5, a new system option internal_tmp_disk_storage_engine can be added to define the engine type of the temporary disk table as InnoDB. Previously, 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 create temporary table. in the past, the default value is MEMORY. do 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 null); 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(11) NOT NULL) ENGINE=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(11) unsigned not null comment 'primary key' );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(11) unsigned NOT NULL COMMENT 'primary key') ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> set default_tmp_storage_engine = memory;Query OK, 0 rows affected (0.00 sec)mysql> create temporary table tmp3 (id int(11) unsigned not null comment 'primary key' );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(11) unsigned NOT NULL COMMENT 'primary key') ENGINE=MEMORY DEFAULT CHARSET=utf8