SQL Pagination: `SELECT SQL_CALC_FOUND_ROWS` vs. `SELECT COUNT(*)` - Which is Faster?

SELECT SQL_CALC_FOUND_ROWS vs. SELECT COUNT(*) for Counting Rows

When paginating data with SQL, two methods exist for determining the total record count: SQL_CALC_FOUND_ROWS and SELECT COUNT(*). Which approach performs faster?

Method 1: SQL_CALC_FOUND_ROWS

This method adds the SQL_CALC_FOUND_ROWS option to the initial SELECT statement. After executing this statement, you can retrieve the total row count by running SELECT FOUND_ROWS().

Method 2: SELECT COUNT(*)

Instead of using SQL_CALC_FOUND_ROWS, this method executes the original query without modifications. The total row count is then obtained by running a separate query: SELECT COUNT(*) FROM table.

Performance Comparison

The optimal choice depends on specific factors such as indexes and database configuration. According to the MySQL Performance Blog, SQL_CALC_FOUND_ROWS can exhibit varying performance, ranging from equivalent to significantly slower (up to 10x slower) than running separate queries.

Recommendation

Many users report that Method 2 (running two queries) generally yields better performance. However, it is advisable to benchmark both methods with representative datasets to determine the optimal approach for a given environment.

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