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