Making Use of SQL_CALC_FOUND_ROWS and FOUND_ROWS
MySQL has some quirky little functions that allow you to actually send hints to the InnoDB engine. SQL_CALC_FOUND_ROWS and FOUND_ROWS are helpful when you want to page through a lot of data using the LIMIT clause. The two functions allow you to get the total number of rows that would have been returned by a query if you hadn’t used the LIMIT clause more efficiently. There isn’t a lot of documentation surrounding the specifics of what’s actually happening in the engine to make this more efficient but it’s simple and fast. Here’s a simple example:
[syntax,mysql_functions.sql,sql]