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