Re: ORDER BY, LIMIT and indexes

[Nikolas Everett <nik9000@xxxxxxxxx>]

On Mon, Aug 5, 2013 at 9:22 PM, Josh Berkus <josh@xxxxxxxxxxxx> wrote:

Ivan,

> Or, more generally, is there some set of circumstances under which the
> catastrophic scenario will happen?

Yes:

SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 100000

This is the "high offset" problem, and affects all databases which
support applications with paginated results, including non-relational
ones like SOLR.  The basic problem is that you can't figure out what is
OFFSET 100000 without first sorting the first 100000 results.

The easiest solution is to limit the number of pages your users can
"flip through".  Generally anyone asking for page 10,000 is a bot
screen-scraping your site, anyway.

Another solution is to build pages from the maximum id you pulled in the last page so page one is:
SELECT * FROM table ORDER BY id DESC LIMIT 10

and page 2 is:
SELECT * FROM table WHERE id > 19 ORDER BY id DESC LIMIT 10
and page 3 is:
SELECT * FROM table WHERE id > 37 ORDER BY id DESC LIMIT 10

and so on.  You build your urls like this:
http://yousite.com/browse
http://yousite.com/browse?after=19
http://yousite.com/browse?after=37

and so on.