Thread: get # of rows while doing SELECT with LIMIT at same time ?

In web applications like say searching it's common to show page sized
subsets of a larger result set from a query.

It usually takes one query to get the count of the # of results in the
query set & another query to get a page sized subset of items to show.
SELECT COUNT(*) FROM albums alb, artists art WHERE alb.artist_id=art.artist_id AND art.artist_name = 'U2'
SELECT alb.album_name, art.artist_name FROM albums alb, artists artWHERE alb.artist_id=art.artist_id AND art.name =
'U2'LIMIT 0,10 

I suspect that since it takes much of the same work to do the count as
it does to do the select it'd be faster to be able to get the total
count & the limited result set in one query.

MySQL has this feature on their to do list & it may already be in
MySQL 4.0.

Is this something that's possible to do in Postgres or can it be added
to the to do list ?

- Sam.

http://www.mysql.com/doc/en/Nutshell_Other_features.html

Functions like SQL_CALC_FOUND_ROWS and FOUND_ROWS() make it possible
to know how many rows a query would have returned without a LIMIT
clause.

On Thu, Feb 06, 2003 at 04:01:12AM -0800, Sam Iam wrote:
> In web applications like say searching it's common to show page sized
> subsets of a larger result set from a query.
> 
> It usually takes one query to get the count of the # of results in the
> query set & another query to get a page sized subset of items to show.
> 
>  SELECT COUNT(*) FROM albums alb, artists art 
>  WHERE alb.artist_id=art.artist_id AND art.artist_name = 'U2'
> 
>  SELECT alb.album_name, art.artist_name FROM albums alb, artists art
>  WHERE alb.artist_id=art.artist_id AND art.name = 'U2' LIMIT 0,10
> 
> I suspect that since it takes much of the same work to do the count as
> it does to do the select it'd be faster to be able to get the total
> count & the limited result set in one query.

In OpenACS we just grab a mid-sized chunk and cache it in RAM. Users
usually only look at the first few pages, so it suffices for most cases.

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
(I)gnore (R)etry (A)bort (M)eltdown