Get total number of rows when using LIMIT? [duplicate]

by

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;

More Related Contents:

  1. MySQL – Get row number on select
  2. Delimiters in MySQL
  3. How to use GROUP_CONCAT in a CONCAT in MySQL
  4. MySQL query / clause execution order
  5. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  6. ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
  7. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  8. Operand Should Contain 1 Column – MySQL NOT IN
  9. Error Code: 1005. Can’t create table ‘…’ (errno: 150)
  10. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  11. On duplicate key ignore? [duplicate]
  12. How to convert timestamp to datetime in MySQL?
  13. MySQL varchar index length
  14. Ruby on Rails 3 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ on OSX
  15. List of Stored Procedures/Functions Mysql Command Line
  16. Best practices for SQL varchar column length [closed]
  17. How to design a multi tenant mysql database
  18. How to get the count of each distinct value in a column? [duplicate]
  19. Get results that fall within marker radiuses from database
  20. Bash script to insert values in MySQL
  21. What are differences between INSERT and UPDATE in MySQL?
  22. MySQL dynamic cross tab
  23. ERROR: Loading local data is disabled – this must be enabled on both the client and server sides
  24. How do I split the output from mysqldump into smaller files?
  25. SELECT INTO Variable in MySQL DECLARE causes syntax error?
  26. Where does MySQL store database files on Windows and what are the names of the files?
  27. MySQL Group By Hours
  28. RMySQL dbWriteTable with field.types
  29. Count duplicates records in Mysql table?
  30. Errno 121, duplicate key on write or update?

Leave a Comment