php MySQL and paging efficiency_PHP tutorial

The most basic paging method:
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...
In the case of small and medium data volumes, this SQL is enough , the only issue that needs attention is to ensure that an index is used:
For example, if the actual SQL is similar to the following statement, then it is better to establish a composite index on the category_id, id columns:
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10

Paging method of subquery:

As the amount of data increases, the number of pages will become more and more, and the SQL of the next few pages may be similar ：

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

In a nutshell, the further the page is paged, the greater the offset of the LIMIT statement will be , the speed will also be significantly slower.

At this time, we can improve paging efficiency through subqueries, roughly as follows:

SELECT * FROM articles WHERE category_id = 123 AND id >= (
SELECT id FROM articles ORDER BY id LIMIT 10000, 1
) LIMIT 10

--------------------------------- ----------

In fact, you can use a method similar to the strategy mode to handle paging. For example, if it is judged to be within one hundred pages, use the most basic paging method, and if it is greater than one hundred pages, Then use the subquery paging method.

http://www.bkjia.com/PHPjc/318979.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/318979.htmlTechArticleThe most basic paging method: SELECT...FROM...WHERE...ORDERBY...LIMIT. .. In the case of small and medium data volumes, such SQL is sufficient. The only issue that needs attention is to ensure that the index is used...