But then you also need to have an index on that key (column), otherwise it's back to a full table scan again.
But, the whole point of the blog post is a good one - signalling the fact that a table scan is being performed when using OFFSET.
I wasn't aware of that, probably I was unconsciously assuming (like everyone else) that using OFFSET and LIMIT is somehow magically "performant". And (apparently) that's not the case.
In general case optimization of the query is based on indexes (and a lot of other other information) anyway. The approach described in post relies on the primary key indexing, but when ordering does not match primary key ordering, then appropriate index is necessary.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
This won't work if I want to sort the rows by a field name (attribute) which is not an ID / Primary Key.
SELECT id, First_Name
from users
WHERE ID > ? // <- this will fail if I don't sort by ID
ORDER by First_name asc
LIMIT 2;
Algorithm remains the same, except instead of ID we should remember from last record the key by which sorting was performed.
But then you also need to have an index on that key (column), otherwise it's back to a full table scan again.
But, the whole point of the blog post is a good one - signalling the fact that a table scan is being performed when using OFFSET.
I wasn't aware of that, probably I was unconsciously assuming (like everyone else) that using OFFSET and LIMIT is somehow magically "performant". And (apparently) that's not the case.
In general case optimization of the query is based on indexes (and a lot of other other information) anyway. The approach described in post relies on the primary key indexing, but when ordering does not match primary key ordering, then appropriate index is necessary.