SQL_CALC_FOUND_ROWS + FOUND_ROWS()

When we want to list contents based on different pages, we have to do: one get a result set , another count the total number of rows. In general, we will use two query sql to complete this process.

1. SELECT id, title FROM articles WHERE TRUE LIMIT $limit, $start
2  SELECT count(id) as total FROM articles WHERE TRUE

From the above query, we may know the MySQL server will execute twice to scan all articles (query 1 will calculate result set size even when this is not needed). If the articles have more 1 million, the two queries need more times.

Fortunately, the MySQL server provide one methods to remember total records at executing the first query. So after executing the first, we just get the total records based on the first query, not executing the second query. Then we will save more half time. Now I give one example to complete this process.

3. SELECT SQL_CALC_FOUND_ROWS id, title FROM articles WHERE TRUE LIMIT $limit, $start
4.  SELECT FOUND_ROWS() as total

The query 3 will scan all records, but the query 4 will not scan all records of the table. The executing efficiency will improve most.

For details about this method, please refer MySQL reference manual from http://dev.mysql.com/doc/refman/5.1/en/information-functions.html

Good luck.