Home  >  Article  >  Backend Development  >  Temporary table in MySQL_PHP tutorial

Temporary table in MySQL_PHP tutorial

WBOY
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.

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630907.htmlTechArticleWhen working on very large tables, you may occasionally need to run many queries to obtain a small amount of data Subset, instead of running these queries against the entire table, let MySQL find out each time...
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn