Created
February 16, 2011 01:26
-
-
Save jodosha/828662 to your computer and use it in GitHub Desktop.
Efficient pagination which avoids the use of offsets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Yes, definitely.
I wrote a Rails plugin for that "Twitter like" pagination: https://github.com/jodosha/more_paginate, you can find a live example here: https://frestyl.com/venues