Re: selects with large offset really slow

  Richard Huxton <dev@archonet.com> wrote:

> explain analyze select l.id, l.url
> from links l
> inner join stats s
> on l.id = s.link_id
> and s.referrer_id = 1
> order by l.url
> limit 100
> offset 90000;

There are three options you might want to look at:

1. Use a temporary table, then select from that for each page.
2. Use a cursor, and just fetch 100 records at a time from it.
3. Cheat and fetch where l.url>=X, remembering X as the highest url from the
last set of results. This of course means pages of results will overlap.

I tried 1 & 2 - both take about the same about of time as the original query :(. How do people deal with paging results from large tables? As is, web site pages take around 30 seconds to load (often timing out).

John