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