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:

// ...
$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'];
// ...

There is also a trick that if you want to insert SQL_CALC_FOUND_ROWS into the statement by the following PHP code:

// ...
$sql = trim($sql);
$exploded = explode(" ", $sql);
$exploded[0] .= " SQL_CALC_FOUND_ROWS ";
$sql = implode(" ", $exploded);
// ...

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

Related Posts

1. MS SQL 2005 (T-SQL) Row Count for Each Table At work I needed a solution to give me a count of how many rows each table contained. I’ve always liked phpMyAdmin’s ability to list all the tables and show their size and row count. I’ve found it immensely helpful. However, I couldn’t find anything similar for SQL Server Manager...