Re: Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks

On Fri, Jun 27, 2008 at 08:22:35PM +0000, Ragnar wrote:
> let us assume your resultset has a a unique column pk, and is ordered on
> column o:
>
> next page
> select * from foo where (o,pk)>(o,?) order by o limit 10;
> (where the ? is the last pk value in previous select)
>
> this method will be able to make use of an index on (o,pk)

Hum, I think I must be missing something. I'm not sure why you're
comparing 'o' to itself and you're not putting any ordering constraint
on the primary key. I think the query should look closer to:

SELECT * FROM foo WHERE (o,pk)>($1,$2) ORDER BY o,pk LIMIT 10;

Or am I going mad?

I'm expecting a table structure somewhat like:

CREATE TABLE foo (
pk TEXT PRIMARY KEY,
value TEXT,
o INT NOT NULL
);

CREATE INDEX foo_ord_idx ON foo (o,pk);

Sam