Efficient pagination on a table with many records

On this post, the author shows how to efficient paginate on a table with million records.

He shows the simplest (and wrong solution) of selecting all data via

SELECT fields FROM table

with all the filtering done in application logic.

Then he shows the classic

SELECT fields FROM table LIMIT X, Y

which fails (proportional to time) when getting the results near the last pages.

Finally he presents the efficient solution using the id field to filter the results with a query of

SELECT fields FROM table WHERE id > X LIMIT Y

which avoids the need to scan the table to get the right offset. Instead this is optimized on the WHERE portion of the query.

As you may guessed, the problem is to get the id offset right. On web applications that present data in a sequential manner this doesn't seem to be a problem.