Re: Pagination - 1 or 2 queries?

On Fri, 5 Sep 2003, CSN wrote:

>
> --- "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:
> > On Fri, 5 Sep 2003, CSN wrote:
> >
> > > Since you usually need to know the total number of
> > > rows a query would return, do you think it's
> > better
> > > to:
> > >
> > > a) Do one query with a LIMIT and OFFSET to get the
> > > results, and another COUNT query to get the total
> > > number of rows?
> > >
> > > b) Do a single query without a LIMIT and OFFSET,
> > then
> > > do a seek or similiar to get at the rows you want?
> >
> > >
> > > Most tutorials, code, etc. I've seen do "a". The
> > > eclipse library does "b".
> >
> > Either way works. Does the eclipse library use a
> > cursor, or grab the
> > whole dataset and then seek on the client side? If
> > it uses a cursor, I'd
> > expect it to be the fastest and simplest
> > implementation. Since a lot of
> > libs are designed to work with MySQL, they often are
> > written in the first
> > method, where select count(*) is quite quick on
> > MySQL, and MySQL doesn't
> > have cursor support.
> >
> > With Postgresql, the cursor is likely to be the
> > faster method.
> >
>
> Eclipse appears to just use pg_fetch_array($result,
> $index). That'd be pretty similiar to a cursor
> wouldn't it? i.e. only the specified rows would be
> sent to the client (but all rows would be in the
> server's memory).
>
> Eclipse's docs make the argument that "b" is better
> because "a" still needs to select/examine all rows
> before doing the LIMIT and OFFSET.

If they aren't explicitly declaring a cursor, then b isn't exactly the
same. If you do:

select * from table order by fieldname

then

$row = pg_fetch_array()

then the whole data set is returned to the client (i.e. php) before we can
get the row. Now, if they do:

begin;
declare bubba as cursor for select * from table order by fieldname;
move forward 100 in bubba;
fetch 5 from bubba;
rollback;

Then you get the same kind of effect, but only 5 rows have to be retrieved
from the database to the client, and pg_fetch_array will now iterate over
those 5 rows only, and then run dry, so to speak.