MySQL Efficient paging: Sub-query Page instances

General MySQL the most basic page-way:

The code is as follows	Copy Code
SELECT * from content ORDER BY id desc limit 0, 10

In the case of small and medium data, such SQL is sufficient, the only problem to be aware of is to ensure that the index is used. As the amount of data increases, the number of pages becomes more numerous, and the next few pages of SQL may look similar:

The code is as follows	Copy Code
SELECT * from content ORDER BY id DESC limit 10000, 10

Word, is the more backward page, the limit statement will be the greater the offset, the speed will be significantly slower.

First look at the basics of pagination:

The code is as follows	Copy Code
Mysql> explain SELECT * from message ORDER by ID DESC LIMIT 10000, 20G 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)

The meaning of the limit 10000,20 scan satisfies the condition of 10020 lines, throw away the preceding 10000 lines, return the last 20 lines, the problem is here, if it is limit 100000,100, need to scan 100100 rows, in a high concurrent application, Each query needs to scan more than 10W lines, performance must be greatly compromised. It is also mentioned that the limit n performance is fine because only n rows are scanned.

The article refers to a "clue" approach to provide some "clues" to the page, such as the SELECT * from message order by ID DESC, descending by ID, 20 per page, currently on page 10th, the current page entry ID is the largest 9527, the smallest is 9500 , if we only provide "prev", "Next" jump (do not provide to the nth page of the jump), then when processing the "previous page" the SQL statement can be:

The code is as follows	Copy Code
SELECT * from message WHERE ID > 9527 ORDER by ID ASC LIMIT 20; When working with the next page, the SQL statement can be: SELECT * FROM: WHERE ID < 9500 ORDER by ID DESC LIMIT 20;

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

The disadvantage is that only "prev", "Next" link form, but our product managers like "< prev 1 2 3 4 5 6 7 8 9 Next page >" Such a link, how to do?

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

The code is as follows	Copy Code
SELECT * FROM: WHERE ID > 9527 ORDER by ID ASC LIMIT 20, 20; Jump to page 13th: SELECT * FROM: WHERE ID < 9500 ORDER by ID DESC LIMIT 40, 20;

The principle is the same, record the current page ID 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 the M value is relatively small, greatly reduce the number of rows scanned. In fact, the traditional limit m,n, the relative offset has always been the first page, so that the more turn to the back, the efficiency is worse, and the above method does not have such a problem.

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

at this point, we can go through 2 different ways:

First, the Sub-query page to improve paging efficiency, floating in the use of SQL statements are as follows:

The code is as follows	Copy Code
SELECT * from ' content ' WHERE ID <= (SELECT ID from ' content ' ORDER by id desc LIMIT.) ($page-1) * $pagesize. ", 1) Order BY id desc LIMIT $pagesize

Why is that? Because the subquery is done on the index, and the normal query is done on the data file, the index file is usually much smaller than the data file, so it will be more efficient to operate. (via) found through explain SQL statements: Subqueries Use the index!

The code is as follows	Copy Code
ID select_type table Type possible_keys key Key_len ref rows Extra 1 PRIMARY Content Range PRIMARY PRIMARY 4 NULL 6264 Using where 2 subquery content index NULL PRIMARY 4 null 27085 Using Index

After drifting easily measured, the use of the sub-page method of paging efficiency than pure limit increased by 14-20 times!

Second, join paging way

The code is as follows	Copy Code
SELECT * from ' content ' as T1 JOIN (SELECT ID from ' content ' ORDER by id desc LIMIT. $page-1) * $pagesize. ", 1) as T2 WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

After my test, the efficiency of join paging and subquery paging is basically one level, and the time consumed is basically the same. Explain SQL statement:

code is as follows	copy code
ID select_ Type table type Possible_keys key Key_len ref rows Extra 1 PRIMARY <derived2> system null NULL NULL nulls 1&NB Sp 1 PRIMARY T1 range PRIMARY PRIMARY 4 null 6264 Using where 2 DERIVED content index NULL PRIMARY 4 null 27085 usin G index