How to use temporary tables in MySQL

Http://dev.firnow.com/course/7_databases/mysql/Mysqljs/20090302/156754.html

When you work on a very large table, 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, instead, MySQL can find a few required records each time, and it may be faster to select a temporary table, 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) the non-temporary table tmp_table.

If you declare that the temporary table is a heap table, MySQL also allows you 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.

----------------------

MySQL optimization: Memory tables and temporary tables
CIC has a very large amount of data. splitting this data involves OLAP knowledge. You need to create many intermediate temporary tables for splitting.
Because temporary tables are used directly to create intermediate tables, the speed is not satisfactory. Therefore, the idea of creating a temporary table as a memory table exists. However, the difference between a memory table and a temporary table is not familiar with it. You need to find the information.
Initially, the temporary table exists after it is created. When the connection is disconnected, the temporary table is deleted, that is, the temporary table exists on the disk. In practice, the temporary table is found to be created and then viewed in the directory. No temporary table file is found (the link is not closed ). therefore, we guess that the data and structure of the temporary table are stored in the memory, rather than on the disk.
In this case, isn't the memory table also in the memory? What is the difference between it and the temporary table? What is their speed?

I found some explanations in the official manual:
The memory storage engine creates tables with contents that are stored in memory. Formerly, these were known as heap tables. memory is the preferred term, although heap remains supported for backward compatibility.

Each memory table is associated with one disk file. The filename begins with the table name and has an extension of. frm to indicate that it stores the table definition.

It can be seen that the memory table stores the table structure on the disk and stores the data in the memory.
And did the following experiments:
Temporary table
Mysql> create temporary table tmp1 (ID int not null );
Query OK, 0 rows affected (0.00 Sec)

Mysql> show create table tmp1;
+ ------- + Response +
| Table | create table |
+ ------- + Response +
| Tmp1 | create temporary table 'tmp1' ('id' int (11) not null) engine = MyISAM default charset = utf8 |
+ ------- + Response +
1 row in SET (0.00 Sec)

Memory table
Mysql> Create Table tmp2 (ID int not null) type = heap;
Query OK, 0 rows affected (0.00 Sec)

Mysql> show create table tmp2;
+ ------- + Response +
| Table | create table |
+ ------- + Response +
| Tmp2 | create table 'tmp2 '(
'Id' int (11) not null
) Engine = memory default charset = utf8 |
+ ------- + Response +
1 row in SET (0.00 Sec)

It can be seen that the temporary table is different from the memory table engine. The default temporary table is MyISAM, while the memory table is memory. You can view the temporary table in the database directory and find that tmp2.frm does not have any files in the tmp1 table. It seems that the actual situation is in line with the official explanation.

So what about speed (the difference between MyISAM and memory )?
Lab started:
Implementation Method: Perform OLAP segmentation on two tens of millions of tables. Two different methods are used to create intermediate tables. Finally, the data in the intermediate table is taken out as required and inserted into the result table.
Objective: To test the speed of a temporary memory table and a temporary table
1. Use create temporary table type = heap to create an intermediate table as a temporary memory table.
2. Use create temporary table to create an intermediate table.

Experiment results:
Temporary memory table: 1 hour
1 11:03:48
1 12:03:39
Temporary table: 1 hour and 17 minutes
2 12:25:28
2 13:42:37

It is found that memory is about 20% faster than MyISAM.

Find the official manual:
As indicated by the name, memory tables are stored in memory. they use Hash indexes by default, which makes them very fast, and very useful for creating temporary tables. however, when the server shuts down, all rows stored in memory tables are lost. the tables themselves continue to exist because their definitions are stored in. FRM files on disk, but they are empty when the server restarts.

It can be seen that memory is indeed very fast, and very useful for creating temporary tables. putting a temporary table and a memory table together is indeed much faster: Create Table tmp2 (ID int not null) Engine Memory;

The creation of memory tables has some restrictions:
Memory tables cannot contain blob or text columns. Heap does not support blob/Text columns.
The server needs sufficient memory to maintain all memory tables that are in use at the same time. Sufficient memory is required at the same time.
To free memory used by a memory table when you no longer require its contents, you should execute delete or truncate table, or remove the table altogether using drop table.
To release the memory, you should execute Delete from heap_table or drop table heap_table.