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
