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:

SELECT COUNT(*) FROM author WHERE name LIKE 'a%';

SELECT name, email FROM author WHERE name LIKE 'a%' LIMIT 10;

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:

SELECT SQL_CALC_FOUND_ROWS name, email FROM author WHERE name LIKE 'a%' LIMIT 10;

SELECT FOUND_ROWS();

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.

Shaurav Kumar
[Intelligrape Software Pvt. Ltd.]