I have a query that includes a LIMIT clause that returns say 10 results at a time out of a total of 100. What I want is also to count the total number of results (ie. 100). Do I have to do a second query without the LIMIT clause then count the number of rows returned? I dont want to do this as the query is quite expensive.
select sql_calc_found_rows * from table .... limit x;
select found_rows();
Take a look at the manual
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
that says:
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.
Execute a second query on your mysql:
SELECT FOUND_ROWS();