How to use temporary tables to accelerate query in MySQL-mysql tutorial

This article describes how MySQL uses temporary tables to accelerate queries. Share it with you for your reference. The specific analysis is as follows: using a MySQL temporary table can sometimes accelerate the query. The following describes how to use a MySQL temporary table to accelerate the query. Sort a subset of a table and create a MySQL temporary table, which sometimes accelerates query. It helps avoid

This article describes how MySQL uses temporary tables to accelerate queries. Share it with you for your reference. The specific analysis is as follows: using a MySQL temporary table can sometimes accelerate the query. The following describes how to use a MySQL temporary table to accelerate the query. Sort a subset of a table and create a MySQL temporary table, which sometimes accelerates query. It helps avoid

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:

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:

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:

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.