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

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