Improving Pagination

Recently I discovered a method to improve the performance of a pagination in MySQL. Generally takes two queries to the database, one for bring the data and another to find out how much data is the total. What is new, though not alter the amount of queries, is that we can make the MySQL server has to process only once.

How does this work?
In the first request, in addition to requesting the data, we say to the server to calculate the total number of rows of the query, then we do a query to get this information.

mysql> SELECT * FROM t SQL_CALC_FOUND_ROWS
-> WHERE id> 100 LIMIT 10;
mysql> SELECT FOUND_ROWS ();


This is especially useful if you use an ORDER BY clause, or a complex consultation with subqueries, multiple JOIN or many calculations.

Check MySQL Manual