PHP MySQL and paging efficiency _php skills

The most basic way to page paging:
SELECT ... From ... WHERE ... ORDER BY ... LIMIT ...
In the case of small and medium data, such SQL is sufficient, the only problem to be noted is to ensure that the index is used:
For example, if the actual SQL resembles the following statement, it is better to establish a composite index on the category_id, id two column:
SELECT * from articles WHERE category_id = 123 ORDER BY ID LIMIT 50, 10

How the subquery is paged:

As the amount of data increases, the number of pages becomes more numerous, and the next few pages of SQL may look similar:

SELECT * from articles WHERE category_id = 123 ORDER BY ID LIMIT 10000, 10

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

At this point, we can use subqueries to improve paging efficiency, 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 similar strategy mode to deal with pagination, such as judging if it is within 100 pages, use the most basic paging, greater than 100 pages, then use the paging method of the subquery.