Discussion on temporary table and derived table of MySQL

About Derived Tables

When the main query contains derived tables, or when the select statement contains union sentence, or when the select statement contains order by clause of one field (group by clause of another field), MySQL needs to automatically create temporary tables to store temporary result sets in order to complete the query. This temporary table is created and maintained by MySQL itself and becomes an automatically created temporary table. For automatically created temporary tables, because the performance of memory temporary tables is superior, mysql always uses memory temporary tables first, and when the memory temporary tables become too large and reach a certain threshold, the memory temporary tables are transferred to external memory temporary tables. That is to say, the external memory temporary table is an extension of the memory temporary table in the storage space. The threshold for transferring an in-memory temporary table to an out-of-memory temporary table is determined by the smaller values of the system variables max_heap_table_size and tmp_table_size.

Derived table 1 is used in from clause. Such as:

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

About temporary tables

When working on very large tables, you may occasionally need to run many queries to get a small subset of a large amount of data. Instead of running these queries on the whole table, let MySQL find a few required records at a time. It may be faster to select records to a temporary table, and then run queries on these tables.

It's easy to create a temporary table by adding the TEMPORARY keyword to a normal CREATE TABLE statement:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)

The temporary table will exist during your connection to MySQL. When you disconnect, MySQL will automatically delete the table and free up the used space. Of course, you can delete the table and free up space while still connecting.

DROP TABLE tmp_table

If a table named tmp_table already exists in the database when you create a temporary table named tmp_table, it will be necessary for the temporary table to mask (hide) the non-temporary table tmp_table.

If you declare the temporary table to be an 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 the HEAP table is stored in memory, you may run a faster query against it than a temporary table on disk. However, the HEAP table is somewhat different from the 1-like table and has its own limitations. See MySQL reference manual for details.

As suggested earlier, you should test temporary tables to see if they are really faster than running queries against a large number of databases. If the data is well indexed, the temporary table may not be fast at 1 o'clock.

1. When the temporary table is disconnected from mysql, the data in the temporary table will be automatically deleted, but this is limited to the table created with the following statement:

Define fields:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)

2) Importing query results directly into temporary tables

CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name

2. In addition, mysql also allows you to create temporary tables directly in memory, because it will be very fast in memory. 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 of the temporary table will be emptied. If you disconnect, it will be automatically emptied. However, it is impossible for your program to connect to the database every time sql is issued (if this is the case, there will be problems you are worried about, if not, there will be no problems), because only by disconnecting the database connection will the data be emptied, and if sql is issued many times in a database connection, the system will not automatically empty the temporary table data.