MySQL SQL_CALC_FOUND_ROWS for pagination performance

Instead of doing 2 queries to get the results and the number of possible results for a pagination on MySQL you can use SQLCALCFOUND_ROWS.

SELECT SQL_CALC_FOUND_ROWS id, name, slug, created
FROM your_table
WHERE slug = 'valid_slug'
LIMIT 0, 25

The query above will return 25 results as expected and to simply get the total number of possible results all you would need to do is:

SELECT FOUND_ROWS();

Just a note: This is great for pagination since it increases performance a bit, but for a normal query it might not be a good thing.

SQLCALCFOUND_ROWS forces MySQL to scan for ALL matching rows, even if they'd never get fetched. Internally it amounts to the same query being executed without the LIMIT clause.

There is a bunch of good info here: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html