get totalCount of records when using limit in one Query

For pagination we generally execute two query ,first for geting resultset by using limit in sql query and second to count the total no. of records . For total no. of records we again execute the same query with count(*).

You would need two queries like these:

[java]
SELECT COUNT(*) FROM author WHERE name LIKE ‘a%’;

SELECT name, email FROM author WHERE name LIKE ‘a%’ LIMIT 10;
[/java]

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.

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.

Queries would look like this:

[java]
SELECT SQL_CALC_FOUND_ROWS name, email FROM author WHERE name LIKE ‘a%’ LIMIT 10;

SELECT FOUND_ROWS();
[/java]

Limitation: Must call second query immediately after the first one(or before next one) because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.In the absence of the SQL_CALC_FOUND_ROWS option in the most recent successful SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement.