Use MySQL temporary tables to accelerate queries

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 in 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.

 

How to view MySQL binary logs

Mysql Log File description

How to relocate the MySQL DATA DIRECTORY

MySQL database table Representation

Location of MySQL DATA DIRECTORY