SQL_CALC_FOUND_ROWS vs. SELECT COUNT(*): Which is Faster for Pagination?

Performance Comparison: SQL_CALC_FOUND_ROWS vs. SELECT COUNT(*)

When seeking the total number of records in a query while using pagination, two methods emerge: SQL_CALC_FOUND_ROWS and SELECT COUNT(*). Determining the optimal approach hinges on various factors.

Method 1: SQL_CALC_FOUND_ROWS

This method employs SQL_CALC_FOUND_ROWS in the initial SELECT query and subsequently uses FOUND_ROWS() to retrieve the total row count. The syntax is as follows:

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

Method 2: SELECT COUNT(*)

Alternatively, a separate query counts the rows using SELECT COUNT(*), after executing the original query. The syntax is:

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

Performance Evaluation

The performance of each method varies based on factors such as index availability and query complexity. One comprehensive analysis by the MySQL Performance Blog suggests that:

• SQL_CALC_FOUND_ROWS is generally slower than running two queries, particularly if indexes are not optimized or the query is complex.
• The performance gap widens with increasing row counts.
• However, for queries with complex expressions or subqueries, SQL_CALC_FOUND_ROWS may outperform SELECT COUNT(*) due to index utilization.

Ultimately, the ideal approach depends on the specific context and application requirements. As noted in the Performance Blog post, "It depends on your indexes and other factors."

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