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 one field (a GROUP BY clause for another field), MySQL needs to automatically create a temporary table to store the temporary result set 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, since memory temporary tables have better performance, MySQL always uses memory temporary tables first, and when the memory temporary table becomes too large, reaches a certain threshold, the memory temporary table is converted to an external temporary table. That is to say, the external temporary table is an extension of the memory temporary table in terms of storage space. The threshold for converting memory temporary tables to external temporary tables 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. For example:

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, not to run these queries on the entire table, but to let MySQL find the required few records each time, and select the records into a temporary table may be faster, and then run queries on these tables.

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

CREATE TEMPORARY TABLE tmp_table
name VARCHAR10) NOT NULL
value INTEGER NOT NULL
)

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

DROP TABLE tmp_table

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

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

CREATE TEMPORARY TABLE tmp_table
name VARCHAR10) NOT NULL
value INTEGER NOT NULL
) TYPE = HEAP

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

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

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

Define fields:

CREATE TEMPORARY TABLE tmp_table
name VARCHAR10) 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

2Another MySQL also allows you to create a temporary table directly in memory because it is in memory, so the speed will be very fast, the syntax is as follows:

CREATE TEMPORARY TABLE tmp_table
name VARCHAR10) NOT NULL
value INTEGER NOT NULL
) TYPE = HEAP

3From the above analysis, it can be seen that the data in the temporary table will be cleared, and you will be automatically cleared when you disconnect the connection. However, it is impossible for you to connect to the database every time you issue a sql (if this is the case, the problem you are worried about will occur, if not, there is no problem), because only when the database connection is disconnected will the data in the temporary table be cleared. The system will not automatically clear the data in the temporary table if multiple sql statements are issued within one database connection.

Statement: The content of this article is from the Internet, the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, does not undergo manual editing, and does not assume relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#w3Declaration: The content of this article is from the Internet, the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, does not undergo manual editing, and does not assume relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#w