Efficient pagination which avoids the use of offsets

pagination.sql

-- 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 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.

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