Re: Way to use count() and LIMIT?

On Tue, Dec 18, 2001 at 05:24:39PM -0700, Mitch Vincent wrote:
> see http://www.php.net/manual/en/function.pg-numrows.php
>
> Still, this isn't what you want I'd say.. That just gives you the number of
> rows returned by a query -- a query that uses LIMIT X return X or less
> records, so that's not the most that pg_numrows() will ever give you is X...
>
> I've run into this before when I did some search engine work with PG, it's a
> pain but I've found no workaround... I ended up having to do two queries,
> one a count() and one to return the records...

Well, there are conflicting requirements. By using LIMIT the database can
choose a plan better suited to giving a few results quickly but would take
forever if it had to do the whole table.

By saying that you want the total number of rows but only want a few back
you're basically asking the database to execute the entire query and then
toss out most of the result.

I gather the reason you don't actually run the whole query is because the
resultset would be too large? But you still want the database to work out
exactly how many there are.

Maybe just go for an estimate of the total? How big do you expect your
results to be?
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Terrorists can only take my life. Only my government can take my freedom.