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. My 2009 Technology Recap Its been two years now that I've been a more or less "serious blogger." I had using the term blogger, since when people hear about blogs, they think of people either detailing their entire lives, or pumping some angel. For me, blogging has just been a way to share information...
2. Presentation: Real Life Scaling Here are my slides for the presentation that I gave at the Utah Open Source Conference on Friday. It was an awesome conference, and I am glad that I was able to be involved. Real Life Scaling: A Tale of Two Websites View more presentations from Justin Carmony. If you...