php mysql php MySQL and paging efficiency

The most basic paging method:
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...
In the case of small and medium data volumes, such SQL is sufficient. The only issue that needs attention is Just make sure to use an index:
For example, if the actual SQL is similar to the following statement, then it is better to build a composite index on the category_id, id columns:
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10
Sub Query paging method:
As the amount of data increases, the number of pages will become more and more. The SQL of the next few pages may be similar to:
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10
In a word In other words, the further the page is paged, the greater the offset of the LIMIT statement will be and the speed will be significantly slower.
At this point, 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
----------------------------------------
You can actually 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; if it is larger than one hundred pages, use the subquery paging method.

The above introduces php mysql php MySQL and paging efficiency, including the content of php mysql. I hope it will be helpful to friends who are interested in PHP tutorials.