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;
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.