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 ?
Date: Thu, 6 Feb 2003 12:27:43 -0700
Message-ID: <20030206192743.GA30532@cc.usu.edu> (raw)
In-Reply-To: <fe7db0d0.0302060401.eaab63f@posting.google.com>
References: <fe7db0d0.0302060401.eaab63f@posting.google.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rmello@cc.usu.edu, thatsamiam@yahoo.com
Subject: Re: get # of rows while doing SELECT with LIMIT at same time ?
In-Reply-To: <20030206192743.GA30532@cc.usu.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox