Mysql uses temporary tables to speed up queries _mysql

This example describes how MySQL uses temporary tables to speed up queries. Share to everyone for your reference. The specific analysis is as follows:

With a MySQL temp table, sometimes you can speed up the query, and here's a detailed description of how to use the MySQL temporary table to expedite the query.

Sorting a subset of a table and creating a MySQL temp table can sometimes speed up queries. It helps to avoid multiple sorting operations and, in other ways, simplifies the work of the optimizer. For example:

Copy Code code 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 the query is to be executed multiple times and more than once, all unpaid customers can be found in a temporary file and sorted by the customer's name:

Copy Code code 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 in the Temp table in the following way:

Copy Code code as follows:

SELECT * from Cust_with_balance
WHERE postcode> "98000"

There are fewer rows in the temporary table than in the primary table, and the physical order is the required order, reducing disk I/O, so the query workload can be drastically reduced.

Note: Temporary table creation does not reflect changes to the primary table. When data is frequently modified in the primary table, be careful not to lose data.

I hope this article is helpful to the design of MySQL database.