mySQL / Codeigniter中返回的行数（不受LIMIT子句影响）

Question

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.

Answer 1

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.