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