MySQL efficient paging: subquery Paging

Generally, the most basic paging method of MySQL is as follows:

Select * from content order by id desc limit 0, 10

In the case of small data volumes, such SQL statements are enough. The only problem that needs to be paid attention is that indexes are used. As the amount of data increases, the number of pages will increase. The SQL statements on the last few pages may be similar:

Select * from content order by id desc limit 10000, 10

In a word, the more paging goes, the larger the offset of the limit statement, and the slower the speed.

In this case, we can use two methods:
1. The paging method of subqueries improves paging efficiency. The SQL statements that are easy to use are as follows:

SELECT * FROM `content` WHERE id <= (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize

After easy measurement, the efficiency of the subquery paging method is 14-20 times higher than that of the pure limit method!

Ii. Join paging Mode

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; 

In my tests, the efficiency of join paging and subquery paging is basically at the same level, and the consumed time is basically the same. Explain SQL statement:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARY<derived2>systemNULLNULLNULLNULL1 1PRIMARYt1rangePRIMARYPRIMARY4NULL6264Using where2DERIVEDcontentindexNULLPRIMARY4NULL27085Using index

Post: http://www.piaoyi.org/php/MySQL-SUBQUERY-index.html