How to Determine Total Result Count in MySQL with Offset and Limit while Paginating?

Determining Total Result Count in MySQL with Offset and Limit

Pagination is a crucial aspect of web development, allowing for efficient display of large datasets across multiple pages. When using offsets and limits in MySQL queries, it's essential to determine the total number of results to calculate the required pagination.

One approach is to run the query twice: once with the limit to retrieve the results to display, and again without the limit to count the total rows. However, this method is inefficient and can be a burden on database resources.

A more efficient solution is to utilize the SQL_CALC_FOUND_ROWS keyword. This keyword informs MySQL to calculate the total number of rows that would have been returned without the limit. The total count can then be retrieved using the FOUND_ROWS() function.

For instance, consider the following query:

SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 20;

To determine the total number of rows, add SQL_CALC_FOUND_ROWS at the beginning of the query:

SELECT SQL_CALC_FOUND_ROWS * FROM table_name ORDER BY id LIMIT 10 OFFSET 20;

After executing this query, use FOUND_ROWS() to retrieve the total count:

$total_results = $this->db->query('SELECT FOUND_ROWS()')->row()->{'FOUND_ROWS()'};

This approach provides an efficient means to determine the total number of results without the need for a separate query. It saves computational resources and ensures optimal database performance, especially for large datasets.

The above is the detailed content of How to Determine Total Result Count in MySQL with Offset and Limit while Paginating?. For more information, please follow other related articles on the PHP Chinese website!