Re: Suspending SELECTs

mark(at)mark(dot)mielke(dot)cc wrote:
> On Tue, Jan 17, 2006 at 08:56:00PM +0100, Alessandro Baretta wrote:
>
>
> What is wrong with LIMIT and OFFSET? I assume your results are ordered
> in some manner.
>
> Especially with web users, who become bored if the page doesn't flicker
> in a way that appeals to them, how could one have any expectation that
> the cursor would ever be useful at all?
>
> As a 'general' solution, I think optimizing the case where the same
> query is executed multiple times, with only the LIMIT and OFFSET
> parameters changing, would be a better bang for the buck. I'm thinking
> along the lines of materialized views, for queries executed more than
> a dozen times in a short length of time... :-)
>
> In the mean time, I successfully use LIMIT and OFFSET without such an
> optimization, and things have been fine for me.
>

Second that.

I do seem to recall a case where I used a different variant of this
method (possibly a database product that didn't have OFFSET, or maybe
because OFFSET was expensive for the case in point), where the ORDER BY
key for the last record on the page was saved and the query amended to
use it filter for the "next' screen - e.g:

1st time in:

SELECT ... FROM table WHERE ... ORDER BY id LIMIT 20;

Suppose this displays records for id 10000 -> 10020.
When the user hits next, and page saves id=10020 in the session state
and executes:

SELECT ... FROM table WHERE ... AND id > 10020 ORDER BY id LIMIT 20;

Clearly you have to be a little careful about whether to use '>' or '>='
depending on whether 'id' is unique or not (to continue using '>' in the
non unique case, you can just save and use all the members of the
primary key too).

Cheers

Mark