* 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. > 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 > page... Ah, cool! That trick never occurred to me. Regards, -- Aristotle Pagaltzis // <http://plasmasturm.org/> _______________________________________________ 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/
