How to Efficiently Get Total Row Count in MySQL Pagination with OFFSET and LIMIT?

Finding Total Results in MySQL Queries for Pagination with Offset and Limit

In pagination scenarios, determining the total number of results is crucial for accurate page calculations. Using offset and limit to retrieve data can lead to concerns about computational efficiency when attempting to calculate the total count from a separate query.

Addressing this issue, MySQL provides a solution with SQL_CALC_FOUND_ROWS. By adding this directive to a query, the database calculates the total number of matching rows without actually fetching them:

SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE category_id = 1 LIMIT 10 OFFSET 20;

After executing this query, the database calculates the total number of rows using another internal query like:

SELECT FOUND_ROWS();

This technique provides an efficient and accurate method of obtaining the total result count.

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