Classic shared MySQL limit query optimization

The following article is mainly on the MySQL limit query optimization of the specific content of the introduction, we all know that the MySQL database optimization is very important. The most common and most needed optimization is limit. The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit is reduced dramatically.

It's also taking 10 data.

 
  
  

  
   
   
SELECT * FROM Yanxue8_visit limit 10000,10
  
   
   
SELECT * FROM Yanxue8_visit limit 0,10
 
  
  

is not a quantitative level.

There are also a lot of five optimization guidelines on limit, which are translated from MySQL manual, although correct but not practical. Today I found an article to write about limit optimization, very good.

Instead of using limit directly, we first get the ID of offset and then use limit size directly to get the data. According to his data, it is significantly better than using limit directly. Here I use the data in two different situations to test. (Test environment WIN2033+P4 dual core (3GHZ) +4g Memory mysqllimit query)

1, offset relatively small time

 
  
  

  
   
   
1.select * from Yanxue8_visit limit 10,10
 
  
  

Run multiple times, keeping the time between 0.0004-0.0005

 
  
  

  
   
   
Select * from Yanxue8_visit Where vid >= (
  
   
   
Select vid from Yanxue8_visit order by vid limit 10,1) limit 10
 
  
  

Run multiple times, keeping the time between 0.0005-0.0006, mainly 0.0006

Conclusion: The direct use of limit is more excellent when offset is smaller. This is obviously the reason for the subquery.

2, when the big offset

 
  
  

  
   
   
SELECT * FROM Yanxue8_visit limit 10000,10
 
  
  

Run more than once and keep the time around 0.0187

 
  
  

  
   
   
Select * from Yanxue8_visit Where vid >= (
  
   
   
Select vid from Yanxue8_visit order by vid limit 10000,1) limit 10
 
  
  

Run several times, the time remained at about 0.0061, only the former 1/3. You can expect the larger the offset, the better the latter.

Later should pay attention to correct their own limit statements, optimize the MySQL

Recommended by People Review

The optimization of MySQL is very important. The other most common and most need to optimize is limit. The limit of MySQL brings great convenience to paging, but when the amount of data is large, the performance of limit is reduced dramatically.

The above related content is the introduction of Mysqllimit query optimization, hope you can have some harvest.