pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedget # of rows while doing SELECT with LIMIT at same time ?
2+ messages / 2 participants
[nested] [flat]
* get # of rows while doing SELECT with LIMIT at same time ?
@ 2003-02-06 12:01 Sam Iam <thatsamiam@yahoo.com>
0 siblings, 1 reply; 2+ messages in thread
From: thatsamiam@yahoo.com @ 2003-02-06 12:01 UTC (permalink / raw)
To: pgsql-sql
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.
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: get # of rows while doing SELECT with LIMIT at same time ?
@ 2003-02-06 19:27 Roberto Mello <rmello@cc.usu.edu>
parent: Sam Iam <thatsamiam@yahoo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Roberto Mello @ 2003-02-06 19:27 UTC (permalink / raw)
To: Sam Iam <thatsamiam@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-02-06 19:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-06 12:01 get # of rows while doing SELECT with LIMIT at same time ? Sam Iam <thatsamiam@yahoo.com>
2003-02-06 19:27 ` Roberto Mello <rmello@cc.usu.edu>
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