2008/5/20 Aristotle Pagaltzis <[EMAIL PROTECTED]>: > * demerphq <[EMAIL PROTECTED]> [2008-05-20 15:10]: >> 2008/5/20 Aristotle Pagaltzis <[EMAIL PROTECTED]>: >> > * demerphq <[EMAIL PROTECTED]> [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 >> perspective.) > > 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. >> page... > > Ah, cool! That trick never occurred to me. Heh so we are even. :-) yves -- perl -Mre=debug -e "/just|another|perl|hacker/" _______________________________________________ List: [email protected] Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/[email protected]/ Dev site: http://dev.catalyst.perl.org/
