SQL SQL_CALC_FOUND_ROWS

What is SQL SQL_CALC_FOUND_ROWS?

SELECT modifier in MySQL that tells the server to count total matching rows ignoring any LIMIT clause.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SQL_CALC_FOUND_ROWS:

SQL SQL_CALC_FOUND_ROWS Full Explanation

SQL_CALC_FOUND_ROWS is a non-standard MySQL SELECT option. When placed immediately after SELECT, it instructs the optimizer to compute the total number of rows that satisfy the query conditions before LIMIT or OFFSET are applied. The count can then be retrieved by calling the FOUND_ROWS() function in the very next statement within the same session. This technique is commonly used for paginated result sets where both the current page and the overall row count are needed.Internally, MySQL either executes an extra filesort or scans the full result set to compute the count, so queries with SQL_CALC_FOUND_ROWS tend to run slower than the same query without it. For complex JOINs, GROUP BY, or ORDER BY clauses, the performance penalty can be significant. The feature was deprecated in MySQL 8.0.17 and may be removed in future releases. Modern MySQL versions recommend running a separate SELECT COUNT(*) query or using window functions instead.

SQL SQL_CALC_FOUND_ROWS Syntax

SELECT SQL_CALC_FOUND_ROWS select_list
FROM   table_reference
[WHERE  conditions]
[GROUP BY expr]
[ORDER BY expr]
LIMIT offset, row_count;

SQL SQL_CALC_FOUND_ROWS Parameters

Example Queries Using SQL SQL_CALC_FOUND_ROWS

-- Paginated request
SELECT SQL_CALC_FOUND_ROWS id, name
FROM   users
WHERE  active = 1
ORDER BY created_at DESC
LIMIT 0, 20;

-- Retrieve the total row count for the same conditions
SELECT FOUND_ROWS();

Expected Output Using SQL SQL_CALC_FOUND_ROWS

First query returns up to 20 user rows
Second query returns a single number equal to the total active users that match, ignoring the LIMIT

Use Cases with SQL SQL_CALC_FOUND_ROWS

Web or API pagination when both the current slice of data and the full row count are required
Quickly prototyping pagination logic without writing a second COUNT(*) statement

Common Mistakes with SQL SQL_CALC_FOUND_ROWS

Forgetting to call FOUND_ROWS() immediately after the SELECT; a different query will reset the value
Assuming other sessions can read the same FOUND_ROWS() value; it is connection-local
Using SQL_CALC_FOUND_ROWS on large result sets and experiencing hidden performance degradation
Expecting the feature to work in PostgreSQL, SQL Server, or other databases

First Introduced In

MySQL 4.0

Frequently Asked Questions

What does SQL_CALC_FOUND_ROWS do?

It directs MySQL to calculate the full number of rows that meet the query conditions before applying any LIMIT or OFFSET so the count can be fetched with FOUND_ROWS().

Is SQL_CALC_FOUND_ROWS deprecated in MySQL 8?

Yes. Starting in MySQL 8.0.17 the feature is deprecated. Future versions may remove it entirely.

When should I avoid SQL_CALC_FOUND_ROWS?

Avoid it on large tables or complex JOINs because it can trigger an expensive full scan or filesort. A separate COUNT(*) query or window function is usually faster.

What alternatives exist for pagination counts?

Use two queries: one SELECT with LIMIT for the page and another SELECT COUNT(*) for the total. In MySQL 8.0+, COUNT(*) OVER() window function can provide both in one query.