MySQL & PHP – SQL_CALC_FOUND_ROWS – An easy way to get the total number of rows regardless of LIMIT

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[0] .= " SQL_CALC_FOUND_ROWS ";
$sql = implode(" ", $exploded);
// ...
[/php]

Hopefully this can help someone looking to optimize their queries while not loosing functionality.