Re: ORDER BY, LIMIT and indexes

On Mon, Aug 5, 2013 at 9:22 PM, Josh Berkus <josh(at)agliodbs(dot)com> 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.