MySQL limit query optimization procedure

The following articles mainly introduce the specific content of MySQL limit query optimization. We all know that MySQL database optimization is very important. Limit is the most commonly used and optimized. MySQL limit greatly facilitates paging. However, when the data volume is large, the performance of limit decreases sharply.

10 data records are also retrieved.

 
 

  
  
select * from yanxue8_visit limit 10000,10   
  
  
select * from yanxue8_visit limit 0,10  
 
 

It is not an order of magnitude.

There are also many five limit optimization guidelines on the Internet, which are translated from the MySQL manual. They are correct but not practical. Today I found an article about limit optimization, which is quite good.

Instead of using limit directly, we first get the offset id and then use limit size to get data. Based on his data, it is much better to use limit directly. Here I use data in two cases for testing. Test environment: win2033 + p4 dual-core (3 GHZ) + 4G memory MySQLlimit query)

1. When the offset value is small.

 
 

  
  
select * from yanxue8_visit limit 10,10 
 
 

Run multiple times and keep the time between 0.0004 and 0.0005

 
 

  
  
Select * From yanxue8_visit Where vid ＞=(  
  
  
Select vid From yanxue8_visit Order By vid limit 10,1  
  
  
) limit 10 
 
 

Run multiple times, and the time is kept between 0.0005-0.0006, mainly 0.0006

Conclusion: When the offset is small, it is better to use limit directly. This is obviously the cause of subquery.

2. When the offset value is large.

 
 

  
  
select * from yanxue8_visit limit 10000,10 
 
 

Run multiple times and keep the time at around 0.0187

 
 

  
  
Select * From yanxue8_visit Where vid ＞=(  
  
  
Select vid From yanxue8_visit Order By vid limit 10000,1  
  
  
) limit 10  
 
 

Run multiple times, with a time of around 0.0061, only 1/3 of the former. It can be predicted that the larger the offset, the higher the latter.

Pay attention to correct your limit statement and optimize MySQL later.

Recommender comment

MySQL optimization is very important. The other most commonly used and most needed optimization is limit. MySQL limit greatly facilitates paging. However, when the data volume is large, the performance of limit decreases sharply.

The above content is an introduction to the optimization of MySQLlimit queries. I hope you will have some gains.