From: thatsamiam@yahoo.com
To: pgsql-sql@postgresql.org
Subject: get # of rows while doing SELECT with LIMIT at same time ?
Date: 6 Feb 2003 04:01:12 -0800
Message-ID: <fe7db0d0.0302060401.eaab63f@posting.google.com> (raw)
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.
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: thatsamiam@yahoo.com
Subject: Re: get # of rows while doing SELECT with LIMIT at same time ?
In-Reply-To: <fe7db0d0.0302060401.eaab63f@posting.google.com>
* 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