mysql paging principles and efficient mysql paging queries

limit 100000/20 is the way I used to page in mysql, and I'm sure you do too, but to be more efficient, to make the code paging a little more efficient, a little faster, so what do we do?

Part 1: Take a look at the basics of paging:

mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20
***************** 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)

If it is limit 100000100100, it needs to scan 100100 rows. In a highly concurrent application, it needs to scan more than 10W rows per query, so the performance must be greatly reduced. It is also mentioned that limit n performance is fine, as only n rows are scanned.

Part 2: An Efficient Pagination Using MySQL report content extension based on several Yahoo engineers: Approach in the mentioned 1 kind of clue, offer page 1 some clues, such as or SELECT * FROM message ORDER BY id DESC, according to the id descending order page, each page 20, is the current page 10, the current page entry id maximum is 1020, the smallest is 1000, if we only provide 1 page, page 1 under such a jump (not provided to the first N jump of the page), then at the time of processing 1 page SQL statement can be:

SELECT * FROM message WHERE id>1020 ORDER BY id ASC LIMIT 20;// Under the 1 page 

For the next page, the SQL statement could be:

SELECT * FROM message WHERE id<1000 ORDER BY id DESC LIMIT 20;// on 1 page 

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

The disadvantage is that we can only provide the link form of the previous page and the next page, but our product manager likes the link mode of "the previous page 1, 2, 3, 4, 5, 6, 7, 8, 9 and the next page" very much. What should we do?

If LIMIT m,n is unavoidable, the only way to optimize the efficiency is to make m as small as 1. We extend the previous clue practice, still SELECT * FROM message BY id DESC, descending page according to id, 20 items per page, the current page is page 10, the current page item id maximum is 2519, the minimum is 2500;

When on page 10 of SQL is as follows:

SELECT * FROM tb_goods_info WHERE auto_id >=2500 ORDER BY auto_id ASC LIMIT 0,20

To jump to page 9, for example, the SQL statement might say:

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 0,20

For example, to skip to page 8, the SQL statement could be written like this:

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 20,20

For example, to skip to page 7, the SQL statement can be written as follows:

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 40,20

Skip to page 11:

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 0,20

Skip to page 12:

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 20,20

Skip to page 13:

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 40,20

The principle is the same as before. Record the maximum and minimum values of the current page id, and calculate the relative offset between the skip page and the current page. As the pages are similar, the offset will not be large, so the value of m is relatively small, greatly reducing the number of rows scanned. In fact, the traditional limit m,n, relative deviation 1 is straight to page 1, so the more you turn to the back, the less efficient, but the above method does not have this problem.

Note ASC and DESC in SQL. If it is ASC, remember to invert 1.

It has been tested in a table of 60W data aggregates and the results are very clear