>A better way that is very similar is to use WHERE and LIMIT instead.I know >that but as I just replied to this thread, if I do that I will then have to >record each columns I use for each table I want scroll cursor. So from the >implementation point of view, using LIMIT & OFFSET is easier.
Qiulang At 2016-03-03 10:42:37, "Darren Duncan" <darren at darrenduncan.net> wrote: >On 2016-03-02 5:02 AM, ?? wrote: >> Here... said "Do not try to implement a scrolling window using LIMIT and >> OFFSET. Doing so will become sluggish as the user scrolls down toward the >> bottom of the list.?. But the page also said "This information is obsolete? >> ... talks about LIMIT & OFFSET, without mentioning that is a bad idea. So my >> question is can I do that or not (will it become sluggish if I do that) ? > >Using LIMIT and OFFSET is generally a bad idea, not just on performance but >also >logically. > >A better way that is very similar is to use WHERE and LIMIT instead. > >Assuming that you are going through pages consecutively, you know what rows >you've already seen, particularly in the prior page. > >Whatever columns you are sorting your result by, take the last row just seen >and >the query for the next page is found by saying WHERE > or < etc the field >values >for that last row. > >So you're sure to get the rows just after the ones you just saw, and later >pages >shouldn't be any slower than earlier ones. > >This approach is also resilient to arbitrary changes to the database between >page views so you don't either repeat rows or skip rows due to offset mismatch. > >-- Darren Duncan > > >_______________________________________________ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
