Here is a little trick I found awhile back. I faced a challenge several months ago where I had to query a large, complex data result from MySQL. I “paged” through the results using LIMIT and OFFSET. However, I wanted to know the total number of rows w/o the LIMIT. The previous coder accomplished this by a query before appending the LIMIT statement at the end. This turned out to be very inefficient due to MySQL put together the 10000+ rows and sent it to PHP ready to be parsed, only to have the function mysql_num_rows() to be executed on it.
Instead I found on the internet (and I lost the link to the article) the MySQL trick “SQL_CALC_FOUND_ROWS.” I believe MySQL 4 and 5 support it. It allows you to place “SQL_CALC_FOUND_ROWS” right after the SELECT statement. Example:
[mysql] SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 0, 10 [/mysql]
This tells MySQL to store a total number of rows that would have been returned regardless of the LIMIT statement. You retrieve this result but calling this query immediately after:
[mysql] SELECT FOUND_ROWS() AS `found_rows`; [/mysql]
So, in PHP you could do the following code:
[php] // ... $sql = "SELECT SQL_CAL_FOUND_ROWS * FROM users LIMIT 0, 10"; $result = mysql_query($sql); $sql = "SELECT FOUND_ROWS() AS `found_rows`;"; $rows = mysql_query($sql); $rows = mysql_fetch_assoc($rows); $total_rows = $rows['found_rows']; // ... [/php]
There is also a trick that if you want to insert SQL_CALC_FOUND_ROWS into the statement by the following PHP code:
[php] // ... $sql = trim($sql); $exploded = explode(" ", $sql); $exploded .= " SQL_CALC_FOUND_ROWS "; $sql = implode(" ", $exploded); // ... [/php]
Hopefully this can help someone looking to optimize their queries while not loosing functionality.
5 thoughts on “MySQL & PHP – SQL_CALC_FOUND_ROWS – An easy way to get the total number of rows regardless of LIMIT”
$sql = implode(‘ SQL_CALC_FOUND_ROWS ‘ , explode(‘ ‘, trim($sql) , 2));
But take care! Be sure, your query separates SELECT with space, not tab or new line from the rest.
Looks like you’re missing the “C” in CALC on line 2.
$sql = “SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 0, 10”;
$sql = “SELECT SQL_CAL_FOUND_ROWS * FROM users LIMIT 0, 10”;
Thanks for the post.
Its very nice in theory… bit me in the butt today on a high traffic operation…
Its fine for small loads, but I’d forget it for heavy stuff…
The suggestions for dynamically adding “SQL_CALC_FOUND_ROWS” to the query are overly complicated. It can be done with a single string function without explode/implode, which would be much more efficient:
$sql = str_ireplace($sql, “SELECT”, “SELECT SQL_CALC_FOUND_ROWS”, 1);
I am new, but facing the same problem.
Can you pls tell me where to put these codes