Re: Pagination - 1 or 2 queries?

--- "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.

http://www.students.cs.uu.nl/people/voostind/eclipse/api/index.html
(PagedQuery)

CSN

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