Today, I wanted to go over a relatively simple MySQL feature that a lot of people don’t understand: SQL_CALC_FOUND_ROWS. To use this mystical keyword, simply put it in your query right after the SELECT statement. For example:
SELECT * FROM USER WHERE id > 10 LIMIT 2,1 --see just second record
SELECT SQL_CALC_FOUND_ROWS * FROM USER WHERE id > 10 LIMIT 2,1
This won’t change your results. It may, however, make your query run slower than when you select just one row the regular way. What this statement does is tell MySQL to find out just how many total records exist that match your criteria (in this case, where id is bigger than 10). For example, let’s assume that the user table has 100 records that have an id bigger than 10, then the query will take as long as it would have taken for the engine to find those 100 records.
The returned result will still be one the records you are expecting (in this case, the second record it found). But here is where the magic starts: If the very next query you run is a special select statement, you will have access to the total that was found. As in:
SELECT FOUND_ROWS(); --returns 100
The MySQL documentation on this subject says:
[SELECT FOUND_ROWS()] returns a number indicating how many rows the first
SELECTwould have returned had it been written without the
LIMITclause. In the absence of the
SQL_CALC_FOUND_ROWSoption in the most recent
FOUND_ROWS()returns the number of rows in the result set returned by that statement.
No matter what your LIMIT clause looks like (such as LIMIT 10, 1), this second query will still return the same number (in this example, 100). Why is this useful? Pagination. Often times, beginners (including me a few years ago) are stuck doing something like this:
SELECT count(*) FROM USER WHERE id > 10 --figure out how many total records there are SELECT * FROM USER WHERE id > 10 LIMIT 50, 1 --get to record #50
People do this because you need the total to know if other matching results exist or what the last page number is.
This requires the engine to run the same query twice. This can be disastrous in cases where that query already takes a very long time to run. By including SQL_CALC_FOUND_ROWS, the overhead of running that count is grouped up with the process of actually retrieving the row of interest. So while the initial query might take a little longer to run than if you hadn’t tried to do a count, it is definitely faster than running the same query twice.
To take this to the next level, your pagination code should omit the use of SQL_CALC_FOUND_ROWS in subsequent page loads by caching the total count in the URL or session.