Possible Duplicate:
Find total number of results in mySQL query with offset+limit
I have a very complex sql query which returns results that are paginated. The problem is to get the total row count before LIMIT I have to run the sql query twice. The first time without the limit clause to get the total row count. The sql query is really complex and I think they must be a better way of doing this without running the query twice.
Since MYSQL 4.0 we can use SQL_CALC_FOUND_ROWS option in query which will tell MySQL to count total number of rows disregarding LIMIT clause. In main query add SQL_CALC_FOUND_ROWS option just after SELECT and in second query use FOUND_ROWS() function to get total number of rows without executing the query.
If you don't need to omit any rows, you can use SQL Server's TOP clause to limit the rows returned. It is placed immediately after SELECT. The TOP keyword is followed by integer indicating the number of rows to return.
To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.
Using LIMIT you will not limit the count or sum but only the returned rows. So your query will return n rows as stated in your LIMIT clause. And since your query actually returns only one row, applying a (non-zero) limit has no effect on the results.
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 faster, as you execute the main query only once. You can read more about SQL_CALC_FOUND_ROWS and FOUND_ROWS() in MySQL docs.
EDIT: You should note that in most cases running the query twice is actually faster than SQL_CALC_FOUND_ROWS
. see here
EDIT 2019:
The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version.
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows
It's recommended to use COUNT
instead
SELECT * FROM tbl_name WHERE id > 100 LIMIT 10; SELECT COUNT(*) WHERE id > 100;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With