Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)

Question

When you limit the number of rows to be returned by a SQL query, usually used in paging, there are two methods to determine the total number of records:

Method 1

Include the SQL_CALC_FOUND_ROWS option in the original SELECT, and then get the total number of rows by running SELECT FOUND_ROWS():

SELECT SQL\_CALC\_FOUND\_ROWS * FROM table WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

Method 2

Run the query normally, and then get the total number of rows by running SELECT COUNT(*)

SELECT * FROM table WHERE id > 100 LIMIT 10;
SELECT COUNT(*) WHERE id > 100;

Which method is the best / fastest?

Answer 1

Well, I know nothing about MySql, so I can only speculate, but SQL_CALC_FOUND_ROWS is a MySql only feature, and if they added a proprietary syntax to handle something which could be done use a standard syntax, one would think that it was done to provide added benefit and one can only assume that is speed.

Answer 2

Use the Method 1, it's the best/fastest because SELECT FOUND_ROWS() didn't query your table again, it only get the value set by the SQL_CALC_FOUND_ROWS.

Answer 3

It depends. See the MySQL Performance Blog post on this subject: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Just a quick summary: Peter says that it depends on your indexes and other factors. Many of the comments to the post seem to say that SQL_CALC_FOUND_ROWS is almost always slower - sometimes up to 10x slower - than running two queries.