MySQL: Get total number of rows when using LIMIT
Every now and then you need to limit the number of rows MySQL returns, i.e. use the LIMIT clause. Result set pagination is by far the most often usage of LIMIT clause, since you usually want to select only rows you’ll be displaying on certain page.
The problem is that for pagination you also need total number of rows in a result set, so you know how many pages you’ll have. This usually means that you need to execute query two times. First query is for counting total number of rows without LIMIT. Second query is exactly the same as the first, just without LIMIT and it will actually retrieve required data. You would need two queries like these:
SELECT COUNT(*) FROM users WHERE name LIKE 'a%'; SELECT name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
Now, this is not such a big problem when you have small result sets and/or simple queries. But if you have a complex query that joins several tables and takes a while to execute – well, you probably wouldn’t want to execute it twice and waste server resources.
Luckily since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS option in your query which will tell MySQL to count total number of rows disregarding LIMIT clause. You still need to execute a second query in order to retrieve row count, but it’s a simple query and not as complex as your query which retrieved the data.
Usage is pretty simple. In you main query you need to add SQL_CALC_FOUND_ROWS option just after SELECT and in second query you need to use FOUND_ROWS() function to get total number of rows. Queries would look like this:
SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10; SELECT FOUND_ROWS();
The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.
Although this solution also requires two queries it’s much more faster, as you execute the main query only once.
You can read more about SQL_CALC_FOUND_ROWS and FOUND_ROWS() in MySQL docs.