A brief discussion on MySQL temporary tables and derived tables

About derived tables

When the main query contains a derived table, or when the select statement contains a union clause, or when the select statement contains an order by clause for a field (a group by clause for another field), MySQL needs to automatically create a temporary table to store the temporary result set in order to complete the query. This temporary table is created and maintained by MySQL itself and is called an automatically created temporary table. For automatically created temporary tables, MySQL always uses the memory temporary table first because the performance of the memory temporary table is more superior. When the memory temporary table becomes too large and reaches a certain threshold, the memory temporary table is converted to an external memory temporary table. In other words, the external memory temporary table is an extension of the memory temporary table in terms of storage space. The threshold for converting a temporary table in memory to a temporary table in external memory is determined by the smaller value of the system variables max_heap_table_size and tmp_table_size.

Derived tables are generally used in the from clause. like:

select * from (select * from table) as t;

About temporary tables

When working with very large tables, you may occasionally need to run many queries to get a small subset of a large amount of data. Rather than running these queries against the entire table, it may be faster to let MySQL find the few records you need at a time, select the records into a temporary table, and then run the queries against those tables.

Creating a temporary table is easy. Just add the TEMPORARY keyword to the normal CREATE TABLE statement:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)

The temporary table will exist for the duration of your connection to MySQL. When you disconnect, MySQL will automatically drop the table and free up the space used. Of course you can drop the table and free up space while still connected.

DROP TABLE tmp_table

If a table named tmp_table already exists in the database when you create a temporary table named tmp_table, the temporary table will necessarily mask (hide) the non-temporary table tmp_table.

If you declare the temporary table to be a HEAP table, MySQL also allows you to specify that it be created in memory:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

) TYPE = HEAP

Because HEAP tables are stored in memory, queries you run against them may be faster than against temporary tables on disk. However, HEAP tables are somewhat different from general tables and have their own limitations. See the MySQL Reference Manual for details.

As suggested earlier, you should test temporary tables to see if they are actually faster than running queries against a large database. If the data is well indexed, the temporary table may not be faster at all.

1. After the temporary table is disconnected from MySQL, the system will automatically delete the data in the temporary table, but this is limited to the table created by the following statement:

Define the fields:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)

2) Import the query results directly into a temporary table

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

2. In addition, MySQL also allows you to create temporary tables directly in memory. Since it is in memory, the speed will be very fast. The syntax is as follows:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

) TYPE = HEAP

3. From the above analysis, it can be seen that the data in the temporary table will be cleared. It will be automatically cleared when you disconnect. However, it is impossible for your program to connect to the database every time you issue a SQL statement (if this is the case, then the problem you are worried about will arise. If not, there will be no problem), because the data will only be cleared when the database connection is disconnected. If you issue multiple SQL statements in a database connection, the system will not automatically clear the temporary table data.