99

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.

Community
  • 1
  • 1
Favourite Onwuemene
  • 4,247
  • 8
  • 28
  • 46
  • 15
    You'll get the advices to use `SQL_CALC_FOUND_ROWS` soon, but the thing is that `COUNT(*)` with the same conditions is more efficient – zerkms Oct 14 '12 at 22:44
  • Have you checked this: http://stackoverflow.com/questions/2229218/what-is-mysql-version-of-rowcount – jtheman Oct 14 '12 at 22:44
  • 1
    Have a look at this: http://stackoverflow.com/questions/5928611/find-total-number-of-results-in-mysql-query-with-offsetlimit – c.hill Oct 14 '12 at 22:44
  • @zerkms : why don't you provide your own answer then? – static_rtti Mar 17 '17 at 09:04
  • 2
    @static_rtti the comment contains enough information and does not need any further elaboration. – zerkms Mar 17 '17 at 09:06

1 Answers1

123

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;
Eran Shabi
  • 14,201
  • 7
  • 30
  • 51
Favourite Onwuemene
  • 4,247
  • 8
  • 28
  • 46
  • 4
    SQL_CALC_FOUND_ROWS is slower than running another query without limit as I understood: http://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ Plus it does not work in subqueries – Dariux Oct 15 '14 at 12:38
  • 6
    SQL_CALC_FOUND_ROWS is most definitely **not** slower in my case which is a somewhat complex query taking anywhere from 2 - 10 seconds. This is very helpful, thank you! – But those new buttons though.. Mar 13 '15 at 14:59
  • 4
    Is there any concurrency concern here? What if another person ran another SQL_CALC_FOUND_ROWS between your SQL_CALC_FOUND_ROWS and your FOUND_ROWS()? I'm using [node.js MySQL plugin](https://github.com/mysqljs/mysql), it allows multi query statements but I'm not sure are all these clauses executed in one transaction. – zipper Jan 31 '18 at 01:45
  • What if you just UNION the SQL_CALC_FOUND_ROWS() as a dummy row to your query? Would this solve the concurrency issue @zipper is mentioning? – zmippie Feb 16 '19 at 15:49
  • The problem with `COUNT` is this : https://github.com/sequelize/sequelize/issues/11692 , when there are multiple JOINS, the count may not return what is expected. – Yanick Rochon Nov 19 '19 at 19:56