[Catalyst] Re: Pager and Cache
demerphq at gmail.com
Tue May 20 15:04:12 BST 2008
2008/5/20 Aristotle Pagaltzis <pagaltzis at gmx.de>:
> * demerphq <demerphq at gmail.com> [2008-05-20 15:10]:
>> 2008/5/20 Aristotle Pagaltzis <pagaltzis at gmx.de>:
>> > * 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.)
>> Er actually you got me there. :-) I had even contemplated
>> posting a corrective follow up so im curious what i'm missing.
>> (Although having the last record of the previous fetch
>> including in the next page is often nice from continuity
> Heh. Well, imagine you have 5 records with equal key values, but
> only the first 2 of them fit at the end of the page you are
> looking at. If you use `>`, you will never see the other 3.
> Of course, the extra record will throw off your count, but that's
> easy to correct: instead of a constant `LIMIT`, add the number of
> equal-key records from the end of the page (which is 1 if there
> was only one such record, of course), and filter them out in code
> on the next page.
Ah ok. Right. I was thinking only of exploiting unique indexes. But
sure, what you say makes sense for non-unique indexes.
>> Theres another trick, use $size+1 when fetching, do not display
>> the +1 record and use it as a flag to indicate that there are
>> more pages available. If you get the extra record show the
>> "next page link", if you dont then you have reached the end of
>> the data set. And in this case the >= is important, as you will
>> want to refact the +1 record as the 0 record on the next
I meant 'refetch' there.
> Ah, cool! That trick never occurred to me.
Heh so we are even. :-)
perl -Mre=debug -e "/just|another|perl|hacker/"
More information about the Catalyst