How to Efficiently Retrieve Total Row Count with LIMIT in MySQL?

Retrieving Total Row Count with LIMIT Conditional

When using a SQL query with a LIMIT clause for pagination, determining the total number of rows becomes a challenge. The standard approach involves executing the query twice, once without the LIMIT clause to obtain the row count and then again with the LIMIT to retrieve the paginated data. This process can be time-consuming for complex queries.

However, there is a more efficient solution introduced in MySQL 4.0.0:

Using SQL_CALC_FOUND_ROWS and FOUND_ROWS()

The SQL_CALC_FOUND_ROWS option can be added to the query after the SELECT statement. This flags MySQL to calculate the total number of rows without considering the LIMIT condition. To retrieve the total row count, a second query using the FOUND_ROWS() function is necessary.

Usage:

SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
SELECT FOUND_ROWS();

Note: It is crucial to execute the second query immediately after the first one, as SQL_CALC_FOUND_ROWS does not permanently store the row count.

Deprecation of SQL_CALC_FOUND_ROWS and FOUND_ROWS()

As of MySQL 8.0.17, the SQL_CALC_FOUND_ROWS query modifier and FOUND_ROWS() function have been deprecated and will be removed in a future MySQL version. It is recommended to use COUNT instead:

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

The above is the detailed content of How to Efficiently Retrieve Total Row Count with LIMIT in MySQL?. For more information, please follow other related articles on the PHP Chinese website!