When working on a very large table, you might accidentally need to run a lot of queries to get a small subset of a large amount of data, not run these queries against the entire table, but let MySQL find the few records needed each time, and it might be quicker to select records to a temporary table
, and then run the query more of these tables.
Creating a temporary table is easy, adding temporary key words 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 automatically deletes the table and frees up the space used. Of course you can delete 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, it will be necessary to mask (hide) the non-temporal table tmp_table.
If you declare that the temp table is a heap, MySQL also promises to create it 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 against it may be faster than the temporary table on the disk. However, the heap table is somewhat different from the normal table and has its own limitations. See MySQL reference manual for details.
As suggested earlier, you should test the 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 temp table may be a little unpleasant.
http://www.bkjia.com/PHPjc/630907.html www.bkjia.com true http://www.bkjia.com/PHPjc/630907.html techarticle when working on a very large table, you might accidentally need to run a lot of queries to get a small subset of large amounts of data, not run these queries against the entire table, but let MySQL find it every time ...
