This article mainly introduces how MySQL uses temporary tables to accelerate queries and analyzes the advantages of using temporary tables for repeated queries. it is a very practical technique, for more information, see the following example. 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:
The 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:
The 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:
The 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.