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
