Use temporary tables in MySQL stored procedures

When you work on a 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 the table 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 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 the 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.

Delimiter |
Create procedure sp_test1 (
In pageno int, in pagesize int,
Out pagecount int
)
Begin
Declare idlower bigint;
Declare idupper bigint;
Declare totalreccount int;

Drop table if exists tmp_table21;
Create temporary table tmp_table21 (
Rowid bigint auto_increment primary key,
Userid bigint
);

Insert into tmp_table21 (userid) Select ID from restcomments;
Set idlower = (pageno-1) * pagesize + 1;
Set idupper = pageno * pagesize;
Select * From tmp_table21;

/* Calculate the total number of pages */
Select count (*) from tmp_table21 into totalreccount;
Set pagecount = totalreccount;
End |
Delimiter;
Note:

Introduction: A customer launches a new project and uses the stored procedure to process user login-related transactions. In the stored procedure, user data needs to be processed. Therefore, they use a temporary table to create a temporary table and then insert and process the data; because the connection pool is used and temporary tables are reused, the temporary table is deleted at the end. The customer used a 16 GB 2950 machine as the MySQL db server, and used LoadRunner to perform a simulated login test. It was found that after the concurrency reached, it would no longer be able to go up, in addition, the peak value is not very stable at the level of more than 0.3 million.
at first, I thought that the performance of the machine had reached the limit. After asking about various situations, I thought it could be improved and optimized. After on-site analysis, it is found that a large number of "waiting for table" , A large number of create temporary table and drop table threads are waiting. Obviously, the bottleneck lies in frequent creation and deletion of temporary tables. MySQL needs to frequently Open and Close table descriptors to cause the above problems. Fortunately, they use the connection pool, otherwise the situation will be worse. We recommend that you change drop table to truncate table to clear the temporary table, it will not worry that the temporary table will not be empty during the next call, saving the frequent processing of table file descriptors, and the number of concurrent users is also stable at more than 0.4 million.