How to use temporary tables in MySQL

When working on a very large table, you may occasionally need to run many queries to get a small subset of a large amount of data, instead of running the query on the entire table, instead of having MySQL find the few records needed each time, it may be quicker to select records to a temporary table, and then run the query more than once.

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 (Ten) is not NULL,

Value INTEGER not NULL

)

Temporary tables will exist during your connection to MySQL. When you disconnect, MySQL will automatically delete the table and release the space used. 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 the name tmp_table temporary table, temporary tables will need to mask (hide) The non-temporary table tmp_table.

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

CREATE Temporary TABLE tmp_table (

Name VARCHAR (Ten) is not NULL,

Value INTEGER not NULL

) TYPE = HEAP

Because the heap table is stored in memory, the query you run on it may be faster than the temporary table on the disk. However, the heap table is somewhat different from the general table and has its own limitations. See the MySQL reference manual for details.

As previously suggested, 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 be a bit unpleasant.