Temporary tables in MySQL. When you work on a very large table, you may need to run a lot of queries to obtain a small subset of a large amount of data instead of running these queries on the entire table, instead, MySQL may need to run many queries to obtain a small subset of a large amount of data every time it finds a table that is working on a very large table, rather than running these queries on the entire table, instead, it allows MySQL to find the required few records each time, and selecting a record to a temporary table may be faster.
And then run the query on these tables.
It is easy to CREATE a temporary table and add the TEMPORARY keyword to the normal create table statement:
Create temporary table tmp_table (
Name VARCHAR (10) not null,
Value INTEGER NOT NULL
)
Temporary tables will exist during your connection to MySQL. When you disconnect, MySQL automatically deletes the table and releases the space used. Of course, you can delete the table and release the space when it is 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 block (hide) non-temporary tables tmp_table.
If you declare that the temporary table is a HEAP table, MySQL also promises to specify to create it 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 the memory, you may query it faster than the temporary table on the disk. However, HEAP tables are somewhat different from general tables and have their own restrictions. For more information, see the MySQL Reference Manual.
As previously suggested, you should test temporary tables to see if they are faster than running queries on a large number of databases. If the data is well indexed, the temporary table may be a little unpleasant.
...
