MySQL FAQ series: When to use temporary tables

Introduction to temporary tables

What is a temporary table: MySQL is used to store some intermediate result sets. 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, complex SQL results in a large number of temporary tables being created

There are two types of temporary tables: memory temporary tables and disk temporary tables. Memory temporary tables use the memory storage engine, and disk temporary tables use the myisam storage engine (disk temporary tables can also use the innodb storage engine. The internal_tmp_disk_storage_engine parameter controls which storage engine to use. The default is the innodb storage engine after MySQL 5.7.6, and the default is the myisam storage engine in previous versions). Use the Created_tmp_disk_tables and Created_tmp_tables parameters to view how many disk temporary tables are generated and all temporary tables (memory and disk) are generated.

MySQL creates temporary tables in the following situations:

1. UNION query;

2. Use the TEMPTABLE algorithm or the view in a UNION query;

3. When the ORDER BY and GROUP BY clauses are different;

4. In table joins, the ORDER BY column is not in the driving table;

5. DISTINCT query and add ORDER BY;

6. When the SQL_SMALL_RESULT option is used in SQL;

7. Subqueries in FROM;

8. Tables created during subqueries or semi-joins;

EXPLAIN Check the Extra column of the execution plan result. If it contains Using Temporary, it means that a temporary table will be used.

Of course, if the amount of data that needs to be stored in the temporary table exceeds the upper limit (tmp-table-size or max-heap-table-size, whichever is larger), then it is necessary to generate a disk-based temporary table.

Disk temporary tables are created in the following situations:

1. The data table contains BLOB/TEXT columns;

2. In the GROUP BY or DSTINCT columns, there are character type columns with more than 512 characters (or binary type columns with more than 512 bytes. Before 5.6.15, only whether it exceeds 512 bytes is considered);

3. In SELECT, UNION, and UNION ALL queries, there are columns with a maximum length exceeding 512 (512 characters for string types and 512 bytes for binary types);

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

Starting from 5.7.5, a new system option internal_tmp_disk_storage_engine is added to define the engine type of disk temporary tables as InnoDB. Before this, only MyISAM could be used. The new system option default_tmp_storage_engine added after 5.6.3 controls the engine type of temporary tables created by CREATE TEMPORARY TABLE. Previously, the default was MEMORY. Do not confuse the two.

See the following example

mysql> set default_tmp_storage_engine = "InnoDB";
-rw-rw---- 1 mysql mysql 8558 Jul 7 15:22 #sql4b0e_10_0.frm -- Temporary table of InnoDB engine -rw-rw---- 1 mysql mysql 98304 Jul 7 15:22 #sql4b0e_10_0.ibd
-rw-rw---- 1 mysql mysql 8558 Jul 7 15:25 #sql4b0e_10_2.frm

mysql> set default_tmp_storage_engine = "MyISAM";
-rw-rw---- 1 mysql mysql 0 Jul 7 15:25 #sql4b0e_10_2.MYD -- Temporary table of MyISAM engine -rw-rw---- 1 mysql mysql 1024 Jul 7 15:25 #sql4b0e_10_2.MYI

mysql> set default_tmp_storage_engine = "MEMORY";
-rw-rw---- 1 mysql mysql 8558 Jul 7 15:26 #sql4b0e_10_3.frm -- Temporary table of MEMORY engine

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.