Re: number of rows returned from SELECT

Hello!

Subject says it all I guess:

What's the most efficient way of determining the number of rows that
_would_ be returned after/for a SELECT? Or is this impossible without...
actually... _doing_ a SELECT?

Basically this is for pagination purposes; given a SELECT statement, say,
'SELECT * from table LIMIT 10 OFFSET 10', I'd like to know how many total
rows would be returned if LIMIT and OFFSET weren't used in the statement.

Basically, same thing this guy was asking for a while ago:
http://www.thescripts.com/forum/thread174930.html

Thanks for the assistance!

> What's the most efficient way of determining the number of rows that
> _would_ be returned after/for a SELECT? Or is this impossible without...
> actually... _doing_ a SELECT?

You can't determine it without running the query. However you can ask
for a estimate. Take a look at EXPLAIN.

On Fri, Jul 28, 2006 at 04:26:24PM -0700, Corey wrote:
> What's the most efficient way of determining the number of rows that
> _would_ be returned after/for a SELECT? Or is this impossible without...
> actually... _doing_ a SELECT?

Doing a SELECT is the only way to know for certain. You could use
EXPLAIN to get the planner's row count estimate but that number can
be inaccurate, especially if the statistics gathered by ANALYZE are
out of date.

> Basically this is for pagination purposes; given a SELECT statement, say,
> 'SELECT * from table LIMIT 10 OFFSET 10', I'd like to know how many total
> rows would be returned if LIMIT and OFFSET weren't used in the statement.

You could first do a "SELECT count(*) FROM ..." or you could use a
cursor and MOVE to the end to find out how many rows there are,
then use MOVE and FETCH to retrieve a certain number of rows starting
from a certain position. For large result sets these queries would
be inefficient because internally the server would visit all the
rows, but the count would be accurate.

--
Michael Fuhr