Re: selects with large offset really slow

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote: On Thu, 6 Feb 2003, John Smith wrote:

>
> There are 90K-100K records in each of two tables. This simple join is
> really slow and the larger the offset, the longer it takes. Anything I
> can do to speed it up (a lot)? I've double-checked and there are
> indexes on everything used for joins and ordering.

>
> ############################
>
> 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;

Hmm, do you have an index on the combination of (url, id)? I don't know
if that'd result in a better plan or not, but it may be able to use that
to help.

Just tried it - no speed improvement I'm afraid :(.

John

---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now