Home > Article > Backend Development > Temporary table in MySQL_PHP tutorial
Temporary table in MySQL_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:03:48911browse
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. Instead of running these queries on the entire table, let MySQL find all the data each time. For the few records needed, it may be faster to select the records into a temporary table
some, and then run queries against these 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 for the duration of your connection 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 have to mask (hide) the non-temporary table tmp_table.
If you declare the temporary table to be a HEAP table, MySQL also allows you to specify that it will 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 faster than temporary tables on disk. 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.