Skip to content

@jodosha /pagination.sql
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Efficient pagination which avoids the use of offsets
-- 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.
@benfyvie

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.

@jodosha
Owner

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.