MySQL Paging algorithm

PERCONA Performance CONFERENCE 2009, several engineers from Yahoo brought a "efficient pagination Using MySQL" Report, there are many bright spots, this article is a further extension on the basis of the original text.

First look at the rationale for 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 10000,20 The meaning of the scan satisfies the condition of 10020 lines, throw away the previous 10000 rows, return the last 20 rows, the problem is here, if it is limit 100000,100, need to scan 100100 lines, in a high concurrency application, Each query needs to scan more than 10W lines, the performance must be greatly compromised. It is also mentioned that limit n performance is not a problem, because only n rows are scanned.

The article mentions a "clue" approach, which provides "clues" to page flipping, such as SELECT * from message order by ID DESC, page by ID Descending, 20 per page, current 10th page, current page entry ID maximum is 9527, the smallest is 9500 , if we only provide jumps such as "previous page", "next Page" (No jump to page N), then in the case of "previous page" the SQL statement can be:

SELECT * from message WHERE ID > 9527 ORDER by ID ASC LIMIT 20;

When processing "Next page", the SQL statement can be:

SELECT * FROM message WHERE ID < 9500 ORDER by ID DESC LIMIT 20;

No matter how many pages you turn, only 20 rows are scanned per query.

The disadvantage is that only the "previous page", "next page" link form, but our product manager very much like "< previous 1 2 3 45 6 7 8 9 Next >" How to Do?

If limit m,n inevitable, to optimize efficiency, only if possible to let M small, we extend the previous "clue" approach, or select * from the message order by ID DESC, according to the ID descending page, 20 per page, is currently the 10th page, The maximum current page entry ID is 9527, the smallest is 9500, such as to jump to page 8th, I read the SQL statement can be written like this:

SELECT * from message WHERE ID > 9527 ORDER by ID ASC LIMIT 20, 20;

Skip to page 13th:

SELECT * FROM message WHERE ID < 9500 ORDER by ID DESC LIMIT 40, 20;

The principle is still the same, record the current page ID of the maximum and minimum value, calculate the jump page and the current page relative offset, because the page is similar, this offset is not very large, so that the M value is relatively small, greatly reducing the number of scanned rows. In fact, the traditional limit m,n, the relative offset has been the first page, so the more turned to the back, the efficiency is worse, and the method given above does not have such a problem.

Note the ASC and DESC inside the SQL statement, and if it is the result of ASC, remember to invert it when it is displayed.

has been tested in a table of total 60W data, and the effect is obvious.

MySQL Paging algorithm