When to use temporary table in mysql

When using temporary tables in mysql: 1. When using the TEMPTABLE algorithm or the view in the UNION query; 2. When using the DISTINCT query and adding ORDER BY; 3. When using "SQL_SMALL_RESULT" in SQL " option; 4. When using the subquery in FROM, etc.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

When to use temporary tables in mysql

MySQL will create temporary tables in the following situations:

1. UNION query;

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

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

4. In table connection, ORDER The column of BY is not in the driver table;

5. When querying DISTINCT and adding ORDER BY;

6. When the SQL_SMALL_RESULT option is used in SQL;

7. Subquery in FROM;

8, subquery or table created during semi-join;

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

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 greater), then you need to generate a disk-based Temporary table.

In the following situations, a disk temporary table will be created:

1. The data table contains BLOB/TEXT columns;

2. In the columns of GROUP BY or DSTINCT, there are character type columns exceeding 512 characters (or binary type columns exceeding 512 bytes, before 5.6.15, only whether it exceeds 512 bytes);

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

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

Starting from 5.7.5, there is a new system option internal_tmp_disk_storage_engine that can define the engine type of the disk temporary table 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 the temporary table created by CREATE TEMPORARY TABLE. In the past, the default was MEMORY. Do not confuse the two.

See the following example:

Recommended learning: mysql video tutorial

The above is the detailed content of When to use temporary table in mysql. For more information, please follow other related articles on the PHP Chinese website!