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