MySQL and pagination

MySQL's limit syntax is much more elegant if compared to the top syntax for MSSQL. It's a natural thing to use it to page pagination.

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 aricles 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