Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can

Chris wrote:

> It's the same as doing a select count(*) type query using the same
> clauses, but all in one query instead of two.
>
> It doesn't return any extra rows on top of the limit query so it's
> better than using pg_numrows which runs the whole query and returns it
> to php (in this example).
>
>
> Their docs explain it:
>
> http://dev.mysql.com/doc/refman/4.1/en/information-functions.html
>
> See "FOUND_ROWS()"
>

Note that from the same page:

"If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how
many rows are in the full result set. However, this is faster than
running the query again without LIMIT, because the result set need not
be sent to the client."

So it is not as cost-free as it would seem - the CALC step is
essentially doing "SELECT count(*) FROM (your-query)" in addition to
your-query-with-the-limit.

I don't buy the "its cheap 'cause nothing is returned to the client"
bit, because 'SELECT count(*) ...' returns only 1 tuple of 1 element to
the client anyway. On the face of it, it *looks* like you save an extra
set of parse, execute, construct (trivially small) resultset calls - but
'SELECT FOUND_ROWS()' involves that set of steps too, so I'm not
entirely convinced that doing a 2nd 'SELECT count(*)...' is really any
different in impact.

Cheers

Mark