`SQL_CALC_FOUND_ROWS vs. COUNT(*): Which Method is More Efficient for Pagination?`

SQL Performance: SELECT SQL_CALC_FOUND_ROWS vs. SELECT COUNT(*)

Consider a scenario where you need to limit the number of rows returned by a SQL query for paging purposes. To determine the total number of records, there are two primary methods:

Method 1: SQL_CALC_FOUND_ROWS

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

Method 2: SELECT COUNT(*)

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

The question arises: which method is more efficient?

The Best Method: It Depends

According to the MySQL Performance Blog, there is no definitive answer. Peter Zaitsev, the author of the blog, suggests that the optimal method depends on index configuration and other factors.

General Consensus

However, many comments in response to the blog post seem to indicate that SQL_CALC_FOUND_ROWS is generally slower than running two queries. The reported performance penalty can be significant, potentially up to 10x slower.

The above is the detailed content of `SQL_CALC_FOUND_ROWS vs. COUNT(*): Which Method is More Efficient for Pagination?`. For more information, please follow other related articles on the PHP Chinese website!