Use subqueries to improve MySQL paging Efficiency

Today, I saw an article in Lao Wang's blog titled "verifying the use of subqueries to improve MySQL paging efficiency ".Article, Very rewarding, summary and sharing ~

For MySQL tables with large data volumes, using limit paging has serious performance problems. For example, in a test conducted by Lao Wang, a table with nearly 10 million rows of records is used to query 30 records after 1,000,000th:

 

SQLCode1: Average Time: 6.6 seconds
Select * From 'cdb _ posts' order by PID limit 1000000, 30

SQL code 2: average 0.6 seconds
Select * From 'cdb _ posts' where pid> = (select PID from 'cdb _ posts' order by PID limit 1000000, 1) Limit 30

To retrieve the content of all fields, the first method must span a large number of data blocks and retrieve the content. The second method can retrieve the content directly based on the index field, which naturally improves the efficiency.

It can be seen that the offset of the limit statement will be larger and the speed gap between the two statements will become more obvious. In practical applications, you can use a method similar to the policy mode to process pages. For example, if the number of pages is less than one hundred, you can use the most basic paging method, with more than one hundred pages, the subquery page is used.