[Catalyst] Re: Pager and Cache

[Aristotle Pagaltzis]

* demerphq <demerphq at gmail.com> [2008-05-20 01:10]:
> If you want to exploit indexes in paging properly you need to
> involve an index in the search criteria and remember the last
> fetched value. IE:
> 
>   select * from Foo where id >= last_id_fetched LIMIT $size

++

(Note to readers: using `>=` instead of `>` here is crucial.
Think about why.)

> Of course this approach is more susceptible to the data
> changing behind your back, but all paging suffers this problem

Actually, this is more stable than `OFFSET` paging: it ignores
any records inserted on in pages you have already seen, so as
long as you only page in one direction, you will never see any
records twice.

> but unlike "standard paging" is has the problem that its not
> "reversible" in the same way that  LIMIT $offset, $size is,
> resulting in "uni-directional" paging.

Just keep track of both the largest and smallest key value on the
last page seen. Then changing directions while paging is easy:
you just use `<=` or `>=` as appropriate.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>