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

Getting Total Row Count with LIMIT

When executing SQL queries with pagination, it's often necessary to obtain the total row count. Typically, this requires running the query twice, once without the LIMIT clause to determine the total count.

However, there's a more efficient way to achieve this without resorting to multiple queries.

SQL_CALC_FOUND_ROWS

Since MySQL 4.0.0, the SQL_CALC_FOUND_ROWS option can be used. It instructs MySQL to calculate the total row count even when a LIMIT clause is present.

To use this option, simply add it after the SELECT statement in the main query. Then, execute a separate query to retrieve the row count using the FOUND_ROWS() function.

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

SELECT FOUND_ROWS();

Note: While SQL_CALC_FOUND_ROWS requires two queries, it's generally faster than executing the main query twice. However, as of MySQL 8.0.17, this option is deprecated and will be removed in a future version.

Alternative: COUNT()

As an alternative, you can use the COUNT() function in a separate query to get the total row count.

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

This method also requires two queries, but it's a more recent and preferred approach.

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