How MySQL uses temporary tables to accelerate queries

How MySQL uses temporary tables to accelerate queries

This article describes how MySQL uses temporary tables to accelerate queries. Share it with you for your reference. The specific analysis is as follows:

MySQL temporary tables can be used to accelerate queries. The following describes how to use MySQL temporary tables to accelerate queries.

Sort a subset of a table and create a MySQL temporary table, which sometimes accelerates query. It helps avoid multiple sorting operations and simplifies the optimizer's work in other aspects. For example:
Copy codeThe Code is as follows: SELECT cust. name, rcVBles. balance ,...... Other columns
SELECT cust. name, rcVBles. balance ,...... Other columns
FROM cust, rcvbles
WHERE cust. customer_id = rcvlbes. customer_id
AND rcvblls. balance> 0
AND cust. postcode> "98000"
Order by cust. name
If this query is executed multiple times but more than once, you can find all the unpaid customers in a temporary file and sort them by customer name:
Copy codeThe Code is as follows: SELECT cust. name, rcvbles. balance ,...... Other columns
SELECT cust. name, rcvbles. balance ,...... Other columns
FROM cust, rcvbles
WHERE cust. customer_id = rcvlbes. customer_id
AND rcvblls. balance> 0
Order by cust. name
Into temp cust_with_balance
Then, query the temporary table in the following way:
Copy codeThe Code is as follows: SELECT * FROM cust_with_balance
WHERE postcode> "98000"
The temporary table has fewer rows than the primary table, and the physical order is the required order, which reduces disk I/O, so the query workload can be greatly reduced.

Note: after a temporary table is created, the modification to the primary table is not reflected. Do not lose data when the data in the master table is frequently modified.

I hope this article will help you design MySQL database programs.