Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can

Mark Kirkwood wrote:
> 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.

Sorry - I created a bit of confusion here. It's not doing the count(*),
it's doing the query again without the limit.

ie:

select SQL_CALC_FOUND_ROWS userid, username, password from users limit 10;

will do:

select userid, username, password from users limit 10;

and calculate this:

select userid, username, password from users;

and tell you how many rows that will return (so you can call
'found_rows()').

the second one does do a lot more because it has to send the results
across to the client program - whether the client uses that info or not
doesn't matter.

The OP didn't want to have to change to using two different queries:
select count(*) from table;
select * from table limit 10 offset 0;

Josh's comment was to do the query again without the limit:
select userid, username, password from users;

and then use something like http://www.php.net/pg_numrows to work out
the number of results the query would have returned.. but that would
keep the dataset in memory and eventually with a large enough dataset
cause a problem.

--
Postgresql & php tutorials
http://www.designmagick.com/