Use mysql temporary table to improve query performance on big table¶

Filtering data on a big table can be a bit slow even if you already created necessary indexes. One way to improve performance is to utilize temporary table.

The mechanism is that, if you need to frequently acess a subset of a very big table, you can save the subset to a temporary table and only access temporary table instead of the big table. The temporary table is smaller and you saved the time of filtering of the big table.

Example¶

Suppose you have a big table called user:

CREATE TABLE `user`(
    `uid` bigint(64) unsigned NOT NULL,
    `name` varchar(64) NOT NULL,
    `age` int unsigned NOT NULL,
    PRIMARY KEY (`uid`)
);

Create a temporary table with necessary columns, you don't have to definite all columns of the big table here:

CREATE TEMPORARY TABLE tmp_user(
    `uid` bigint(64) unsigned NOT NULL,
    `name` varchar(64) NOT NULL,
    PRIMARY KEY (`uid`)
);

Copy the subset of the big table to the temporary table:

INSERT INTO tmp_user
SELECT uid, name FROM user
WHERE age > 20;

Then you can query from the temporary table directly like normal tables:

SELECT * FROM tmp_user;