Re: speed w/ OFFSET/LIMIT

On Tue, 27 May 2003, Damien wrote:

> I'm running a pretty simple select query on a pretty large table (70000
> records). This table has some "flag" fields, each one textually explained by
> another table ( flag VARCHAR(2), flag_details VARCHAR(60))
>
> SELECT t.* , t1.flag1_details , ... , tn.flagn_details
> FROM table t
> NATURAL JOIN t1
> NATURAL JOIN ...
> NATURAL JOIN tn
> ORDER BY main_field OFFSET x LIMIT 50
>
> There is no where statement, this query is for display purposes on a web page.
> My problem is, where OFFSET is low, execution takes only a few milli-seconds,
> but where OFFSET is high (50 last results for example), execution can take 10
> seconds...
> Is there anything I can do to speed up such a query ?

Can you send exact query and explain analyze output for each?  Since it
has to get the x+50 I'm not sure what can be done, but the explain output
will help.

As a side note, the workaround in your following message works as long as
the joins give only one match, but won't if they don't (the results are
different in that case).