How Can I Efficiently Get Total Result Count in MySQL Pagination While Using LIMIT and OFFSET?

Obtaining Total Results using MySQL Offset and Limit

Pagination involves retrieving subsets of data from a database based on offset and limit values. However, to determine the total number of pages, knowledge of the total number of results is required. This article presents a programming question pertaining to an efficient method for calculating this total.

Question:

In PHP/MySQL, a user implementing pagination encounters a need to determine the total number of results for a given query, even when employing offset and limit parameters. The concern is that executing a second query without the limit constraint to count rows may prove computationally wasteful. Is there a more resource-friendly approach?

Answer:

Yes, there is an optimized solution for this scenario. By utilizing the SQL_CALC_FOUND_ROWS keyword at the beginning of the initial query, MySQL will perform an automatic count of all matching rows, regardless of offset or limit restrictions. This eliminates the need for a second query.

Here's how to modify your query with SQL_CALC_FOUND_ROWS:

SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE category_id = ? LIMIT ?, ?

Once this modified query is executed, you can execute the following query to retrieve the total count of matching rows:

SELECT FOUND_ROWS()

By using SQL_CALC_FOUND_ROWS, you can efficiently retrieve both the paginated results and the total result count with a single database call.

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