Re: Pagination - 1 or 2 queries?

--- "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:
> 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.
>

Ah, I think you're right ;). PG would get all the
rows, then pass them all on to PHP, then scripts
access whichever ones they want.

With Mysql it looks like mysql_unbuffered_query would
avoid sending all rows to PHP.

PHP also has asynchronous methods for PG:
pg_send_query, pg_get_result, etc. Not sure if they'd
have a similiar effect, but probably better off using
a cursor at any rate :).

CSN

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com