Detailed introduction to MySQL temporary tables and derived tables

MySQLIn certain scenarios when processing requests, the server creates internal temporary tables. That is, the table is processed in memory using the MEMORY engine, or stored on disk using the MyISAM engine. If the table is too large, the server may transfer the temporary table in memory to the disk.

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 of a field (group by clause for another field), in order to complete the query, MySQL needs to automatically create a temporary table to store the temporary result set. 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 the memory temporary table is superior, MySQL always uses the memory temporary table first. When the memory temporary table becomes too large and reaches a certain threshold, the memory temporary table will be dumped. It is an external temporary table. In other words, the external storage temporary table is an extension of the memory temporary table in the storage space. The threshold for transferring a memory temporary table to an external memory temporary table 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. 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 obtain a small subset of a large amount of data Rather than running these queries against the entire table, it may be faster to have MySQL find the few records needed at a time, select the records into a temporary table, and then run the query on those tables.

Creating a temporary table is easy, 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 while you are connected to MySQL. When you disconnect, MySQL will automatically drop the table and free up the used space. 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 need to be masked (hidden) ) 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 the HEAP table is stored in memory, the queries you run on it may be larger than Temporary tables on disk are faster. However, HEAP tables are somewhat different from ordinary 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 fast 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 only limited to tables created with the following statement:

Define fields:

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)

2) Directly import the query results into the 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, because everything in memory 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, we can see that the data in the temporary table will be cleared. It will be cleared automatically when you disconnect the connection, but it is impossible to issue SQL every time in your program. Just connect to the database once (if this is the case, then there will be the problem you are worried about, if not, there will be no problem), because the data will be cleared only when the database connection is disconnected. If you issue multiple sql in a database connection, the system Temporary table data will not be cleared automatically.

The above is the detailed content of Detailed introduction to MySQL temporary tables and derived tables. For more information, please follow other related articles on the PHP Chinese website!