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 artWHERE 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.