David Jenks wrote:

>In regard to limit m, n, are there any cases when the server could avoid
>loading all rows into memory, sorting them, then skipping the first m-1,
>and returning those requested (for instance by using indexes)? (Answers
>for the current optimizer and a hypothetical perfect one both welcome).

Yes, there are two cases that come to mind. The first is when
the result set is not sorted. The other is when the result set
is sorted in such a way that the engine can read the rows in
index order. At one time, the optimizer (which I've avoided of
late) recognized the special case that FIRST <n> .... SORTED BY
should walk an index (if one exists) rather than retrieving the
whole set and sorting it. However when you get into joins and
result sets that are ordered by terms in different tables, the
optimizer reverts to the find 'em and sort 'em strategy.

Obviously, the cost of the latter (to the engine) is s * i,
where 's' is the size of the total result set and 'i' is the
number of iterations of the query. It's only the cases where
the engine can avoid building the whole result set every time
that my other formula applies ... and my sample computation
was off by a power of ten ... sigh.


Regards,

Ann
www.ibphoenix.com
We have answers.