The following SQL snippet shows a neat way to do queries when implementing paged results.

I have tested the snippet on MySQL 5 only.

SELECT SQL_COUNT ROWS *, tableName.id, tableName.someCol
FROM tableName
LIMIT 0,25;

SELECT SELECT FOUND_ROWS();

For example, say the above table tableName has a total of 100 rows, running the above queries one after another would first return a result set of 25 rows, then the second query would return 100, the total number of rows.

It may be interesting also to see if there is a performance difference between a 3 or a 2 query approach, as the MySQL manual only shows running the SQL_COUNT_ROWS * statement on it’s own, unlike with columns as I have done here.

My hunch is that 2 queries would be faster but maybe not for tables with millions of rows?

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows