Efficient MySQL paging statement

Only good article: www.zhangpingyong.commysql-pagination-code.html PERCONAPERFORMANCECONFERENCE2009, several engineers from Yahoo brought a EfficientPaginationUsingMySQL report, there are many highlights, this article is based on the further extension of the original. First, let's take a look at the paging

Only good article: http://www.zhangpingyong.com/mysql-pagination-code.html percona performance conference 2009, several engineers from Yahoo brought a report on Efficient Pagination Using MySQL, there are many highlights, this article is a further extension based on the original article. First, let's take a look at the paging

Only good article: http://www.zhangpingyong.com/mysql-pagination-code.html

On percona performance conference 2009, several Yahoo engineers brought a "Efficient Pagination Using MySQL" report, which has many highlights. This article is a further extension based on the original article.
First, let's take a look at the basic principles of paging:
Mysql> explain SELECT * FROM message order by id desc limit 10000, 20/G
* *************** 1. row **************
Id: 1
Select_type: SIMPLE
Table: message
Type: index
Possible_keys: NULL
Key: PRIMARY
Key_len: 4
Ref: NULL
Rows: 10020
Extra:
1 row in set (0.00 sec)
Limit, 20 means to scan 10020 rows that meet the condition, discard the first 10000 rows, and return the last 20 rows. The problem is here. If it is limit 100000,100, You need to scan 100100 rows, in a highly concurrent application, more than rows need to be scanned for each query, and the performance is definitely compromised. The performance of limit n is okay because only n rows are scanned.

SELECT * FROM message WHERE id> 9527 order by id asc limit 20;

SELECT * FROM message WHERE id <9500 order by id desc limit 20;
No matter how many pages are displayed, only 20 rows are scanned for each query.

If LIMIT m and n are unavoidable, We need to optimize efficiency BY minimizing m as much as possible. We need to extend the previous "clue" approach, or SELECT * FROM message order by id DESC, page by id in descending order. There are 20 entries per page. Currently, there are 10th pages. The maximum id of the current page is 9527, and the minimum is 9500. For example, to jump to 8th pages, the SQL statement I see can be written as follows:
SELECT * FROM message WHERE id> 9527 order by id asc limit 20, 20;
Jump to page 13th:
SELECT * FROM message WHERE id <9500 order by id desc limit;
The principle is the same. It records the maximum and minimum values of the current page id, and calculates the relative offset between the jump page and the current page. Because the page is similar, this offset is not very large, in this case, the m value is relatively small, greatly reducing the number of scanned rows. In fact, the relative offset of the traditional limit m, n has always been the first page. The more we move to the next page, the worse the efficiency, and the above method does not have such a problem.
Note the ASC and DESC in the SQL statement. If the result is obtained by ASC, remember to put it upside down when it is displayed.
It has been tested in a table with a total data volume of 60 million, and the effect is very obvious.