Using OFFSET means you have to sort and count all the records you're skipping before getting to the ones you want, and using WHERE means you can avoid counting and only sort the ones you're not skipping. That is how it is, regardless of what that document you saw says. -- Darren Duncan
On 2016-03-02 7:10 PM, ?? wrote: > So according to your answers and others, this limitation is always there even > the document said "obsolete" ? Just want to double confirm. > > > Thanks! > > > Qiulang > > > At 2016-03-03 11:06:08, "Darren Duncan" <darren at darrenduncan.net> wrote: >> On 2016-03-02 6:48 PM, ?? wrote: >>>> 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 >> >> You have to record the columns anyway in order to know what you're sorting >> your >> results by, this is just reuse. Or if not, then maybe you should bite the >> bullet and record that extra info. >> >> If you don't want to do that in order to simplify things, then you live with >> the >> limitations and sluggishness of LIMIT and OFFSET, as those limitations are >> systemic to LIMIT and OFFSET. >> >> -- Darren Duncan >> >>> 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
