> I want to confirm about OFFSET.
> (Sorry. There are so many mails for me to check that I can't follow
> what(where) it is now.)
>
> I think OFFSET was the offset for indices when
>
> SELECT *
> FROM tab
> WHERE col1 < some_unknown_value
> ORDER BY col1
> USING INDEX tab_idx(452) COUNT 100
> or
> SET INDEX TO tab_idx START 452 COUNT 100
>
> by Bruce
>
> The definition of OFFSET was changed to mean the offset for resultset
> (the set of target rows(without OFFSET)) now ?
>
> such as
> SELECT ... [LIMIT count [, offset]]
>
> by Jan
>
> Thanks.
Yes, old meaning was index size to scan, new meaning is result size to
scan.
The old and new meaning are the same if there is an index matching the
ORDER BY. The old meaning was not workable if there was not matching
index, which is bad. In a join situation, the old meaning offered a few
more optimization possibilities, because you could do the OFFSET on the
index rather than computing the join, then the OFFSET. However, the
number of result rows could vary in a join situation, that that is weird
for people. Add to that there is no logical way to know you have
reached the end, because a list of 100 index entries could return zero
rows. That is also bad. And I don't think people are going to be
seeking into the middle of large result sets very often, so the new
meaning of counting the result rows is the best way to go.
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026