Re: speed w/ OFFSET/LIMIT

Damien wrote:
> Hi !
>
> 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...

This is because of the way OFFSET/LIMIT works internally. It basically
run's your query, throwing away (but fully creating) the first OFFSET
result rows, and aborting the query when reaching LIMIT+1 result rows
after that.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #