Get the total number of rows when using LIMIT?

Question

Possible duplicate:
Find the total number of results in mySQL query with offset + limit

I have a very complicated sql query that returns paginated results. The problem is to get the total number of rows before the LIMIT. I need to execute sql query twice. The first time without a limit clause to get the total number of rows. The SQL query is really complex, and I think they should be the best way to do this without having to execute the query twice.

Answer 1

Fortunately, with MySQL 4.0.0, you can use the SQL_CALC_FOUND_ROWS parameter in your query, which tells MySQL to calculate the total number of rows without considering the LIMIT clause. You still need to execute the second query to get the number of rows, but this is a simple query, not as complex as your query that retrieved the data. The use is pretty simple. In the main query, you need to add the SQL_CALC_FOUND_ROWS parameter immediately after SELECT, and in the second query you need to use the FOUND_ROWS () function to get the total number of rows. The queries will 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 the second query immediately after the first, because SQL_CALC_FOUND_ROWS does not save the number of rows anywhere. Although this solution also requires two queries much faster, since you execute the main query only once. You can learn more about SQL_CALC_FOUND_ROWS and FOUND_ROWS () in MySQL docs.

EDIT: It should be noted that in most cases, query execution is twice faster than SQL_CALC_FOUND_ROWS. see here