SEEK vs OFFSET

In some UseCases, pagination is used to keep the amount of data that is sent small. For this purpose, OFFSET is often used in the database:

SELECT *
FROM users
ORDER BY ID
LIMIT 500 OFFSET 500;

However, the OFFSET must go over all previous entries. No index will help. The larger the OFFSET becomes, the more complex the query becomes.

Another possibility is the seek.

SELECT *
FROM users
WHERE ID > :lastId
ORDER BY ID
LIMIT 500;

We only have to remember the last ID (or unique value), sort by it and filter out all previous ones. The database can fall back on the index.

What to watch out for:

If a user has the option of skipping pages, he or she must first look up the corresponding ID:

SELECT ID
FROM users
ORDER BY ID
LIMIT 1 OFFSET 39999;

Since we are only looking for the ID here, we can fall back on the index. You can also search for several values, but then they must all be contained in a covering index.

See more details https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/