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.

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

  1. Or just:

    $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.

    Like

  2. Looks like you’re missing the “C” in CALC on line 2.

    $sql = “SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 0, 10”;
    instead of
    $sql = “SELECT SQL_CAL_FOUND_ROWS * FROM users LIMIT 0, 10”;

    Thanks for the post.

    Like

  3. 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…

    Like

  4. 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);

    Like

  5. Hey, Michael
    I am new, but facing the same problem.

    Can you pls tell me where to put these codes

    Like

Leave a Reply to fakhre Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close