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:

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:

So, in PHP you could do the following code:

PLAIN TEXT
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'];


// ... 

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

PLAIN TEXT
PHP:




// ...


$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. MySQL - Does Table Exist w/o Throwing Errors There are times where you would like to know if a table exists before executing an query. Most solutions require having MySQL throw an error saying "table does not exist," but I prefer a cleaner way. I found on this forum post a clean way to do it: PLAIN TEXT...
2. Comcast Limit to 250GB a month for Residential According to Slashdot: Comcast has confirmed that all residential customers will be subject to a 250 gigabyte per month data limit starting October 1. 'This is the same system we have in place today,' Comcast wrote in an amendment to its acceptable use policy. 'The only difference is that we...
3. PHP Design - Biggest Database Oversights Over the last three years I've had the opportunity to work on several PHP projects, some of them having grown rapidly and required to scale quickly. Three in particular have been a fantastic learning experience for me. Now I don't consider myself a total expert, but I thought I would...
4. 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...
5. Web Development 10-Years Ago & Now This week's blogging challenge was our computer software now and then. Lorelle talked about the different versions of WordPress over time. I thought it was an interesting article. 10 Years Ago I started web development back in 1997. My tools back then were: Notepad - That right, the one and...