Re: Pagination - 1 or 2 queries?

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.