datispars blog

Using temporary table to increase MySQL query performance

Sometimes you need to get reports or something like, that runs a complex query which includes `JOIN`,  `GROUP BY`, `HAVING` and etc. The time of running is depends on amount of records and complexity. To run queries faster we can use temporary tables.

How Temporary table helps us

• It is faster to get data from temporary table instead of getting data form DB tables with several filter conditions.
• Temporary tables are connection specific. When the current client session terminates all the temporary tables are automatically deleted.
• Temporary table with same name can be created with in different connections.

How to use Temporary table in MySQL?
Creating temporary table is the same as creating regular table. I created the `user_usage_report` temporary table using below command:

CREATE TEMPORARY TABLE user_usage_report
SELECT u.id, o_u.size AS out_size, i_u.size AS in_size
FROM user u
INNER JOIN out_usage o_u ON o_u.user_id=u.id
INNER JOIN in_usage i_n ON i_n.user_id=u.id
WHERE
   o_u.start_date BETWEEN '2009-01-01' AND NOW()
  AND
   i_u.start_date BETWEEN '2009-01-01' AND NOW()
GROUP BY u.id

Next I run below select query through the same connection:

SELECT id, in_size, out_size FROM user_usage_report ORDER BY in_size, out_size

After using the temporary table, I realized that it acquires results several times faster than pure select. Just try it!

Tags: MySQL, Optimization
Posted in MySQL