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/
