|-- Let's say we want to paginate bands, ordered by name.|
|-- The main problem with the use of offsets is the waste of loaded records.|
|-- Math: If you want to load the 7th page (30 artists per page) you will load|
|-- 210 records (30 * 7), to just use 30 of them. As you can imagine this is|
|-- more inefficient as the page number grows.|
|-- The alternative is to use the order column(s) as condition, in order to|
|-- exclude the already loaded records.|
|-- We just fetch the first page as usual:|
|SELECT * FROM `artists` ORDER BY `name` ASC, `artists`.`id` ASC LIMIT 30;|
|-- Now, imagine that the last artist was named "A Fine Frenzy" with 4427 as primary key value:|
|SELECT * FROM `artists` WHERE ((name > 'A Fine Frenzy') OR (name = 'A Fine Frenzy' AND `artists`.`id` > 4427)) ORDER BY `name` ASC, `artists`.`id` ASC LIMIT 30;|
|-- The first part of the WHERE condition is self-explanatory, the second part (after the OR) is for disambiguate the records, just in case you have two artists with the same name.|
|-- In this case, just 30 records are loaded, and there aren't no pitfalls when the page number is high.|
This comment has been minimized.
This comment has been minimized.Show comment Hide comment
This will only boost performance if you require that users can only advance forward one page at a time. For instance, if a user is currently on page 1 and they want to move to page 7, using this technique will actually cause much more overhead than using the standard limit/offset approach. I figure the typical use case is not really for "pagination" per say, but rather a "show me more results" type of implementation where data is appended to what is already being displayed in a list.