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
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable.
Sign up now
