Date: Thu, 6 Feb 2003 12:27:43 -0700
From: Roberto Mello <rmello@cc.usu.edu>
To: Sam Iam <thatsamiam@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: get # of rows while doing SELECT with LIMIT at same time ?
Message-ID: <20030206192743.GA30532@cc.usu.edu>
References: <fe7db0d0.0302060401.eaab63f@posting.google.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
In-Reply-To: <fe7db0d0.0302060401.eaab63f@posting.google.com>
User-Agent: Mutt/1.4i

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