Which is fastest SELECT SQLCALCFOUNDROWS FROM table or SELECT COUNT

Optimizing database queries is a perpetual quest for developers and database administrators alike. A common challenge arises when you need to retrieve a subset of records for display, typically for pagination, but also require the total number of matching records without fetching the entire dataset. This dilemma often pits two primary SQL approaches against each other: SELECT SQL_CALC_FOUND_ROWS FROM table and a separate SELECT COUNT() query. Understanding which is fastest, and under what circumstances, is crucial for building scalable and responsive applications. This article delves deep into the mechanics, performance implications, and best practices for both methods, providing a definitive guide to help you make informed decisions.

Understanding SELECT SQL_CALC_FOUND_ROWS

The SQL_CALC_FOUND_ROWS option in MySQL is designed to solve the pagination problem by allowing you to retrieve a limited set of rows while simultaneously getting the total number of rows that would have been returned if the LIMIT clause had not been present. It works by adding the SQL_CALC_FOUND_ROWS keyword immediately after SELECT in your main query. Subsequently, you execute a separate query, SELECT FOUND_ROWS();, to retrieve the total count.

For example, if you want to display the first 10 products from a filtered list of 1000, you would run SELECT SQL_CALC_FOUND_ROWS FROM products WHERE category_id = 5 LIMIT 0, 10; followed by SELECT FOUND_ROWS();. The apparent advantage here is the perceived simplicity of getting both data and total count with what seems like a single underlying operation. However, this simplicity often masks significant performance overhead, especially with large datasets or complex queries. MySQL’s official documentation provides more technical details on its usage and limitations, which is always a valuable resource to consult for specifics.

One of the key misconceptions about SQL_CALC_FOUND_ROWS is that it’s inherently more efficient due to fewer round trips to the database. While it does reduce the number of explicit queries, the database engine still has to process the full result set internally to calculate the total row count before applying the LIMIT clause. This means it doesn’t benefit from the early termination that a simple LIMIT query would normally provide, leading to potentially much longer execution times than expected, particularly in scenarios where the total potential result set is massive.

Understanding SELECT COUNT()

SELECT COUNT() is a fundamental SQL aggregate function used to return the number of rows in a table or the number of rows that match a specified criterion. When used for pagination, it typically involves executing two separate queries: one to fetch the actual data with a LIMIT clause, and another SELECT COUNT() query to get the total number of records that satisfy the filtering conditions. This method is often favored for its explicit nature and, critically, its superior performance characteristics in many real-world scenarios.

Consider the same product example: you would execute SELECT FROM products WHERE category_id = 5 LIMIT 0, 10; and then a separate SELECT COUNT() FROM products WHERE category_id = 5;. While this involves two distinct queries and thus two database round trips, the COUNT() operation itself is highly optimized. For MyISAM tables, a simple COUNT() without a WHERE clause is extremely fast because the row count is stored directly in the table metadata. For InnoDB tables, or when a WHERE clause is present, MySQL still employs various optimizations, such as using suitable indexes to quickly count matching rows, rather than scanning the entire table.

The efficiency of COUNT() stems from its ability to leverage indexes effectively. If an index covers the columns used in the WHERE clause, the database can count matching entries directly from the index, which is much faster than scanning the actual data rows. Even when no specific index can be used to optimize the count, the database still performs a full scan more efficiently than SQL_CALC_FOUND_ROWS, as it’s not simultaneously retrieving and discarding full row data. You can explore more about how COUNT() works and its various forms on the official MySQL Question & Answer :

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(*) FROM table WHERE id > 100; 

Which method is the best / fastest?

It depends. See the MySQL Performance Blog post on this subject: 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.