How to Count Total Query Results in MySQL with Offset and Limit Optimization?

Fetching Total Query Results in MySQL with Offset and Limit

In pagination scenarios, it is crucial to determine the total number of results in a MySQL query to calculate the total number of pages required. One approach involves running the query twice, once with the limit to retrieve the paginated results and another without the limit to count the total rows. However, this approach may be inefficient.

Enter SQL_CALC_FOUND_ROWS: A more optimized solution is to utilize the SQL_CALC_FOUND_ROWS modifier in your initial query. By including this modifier, MySQL computes the total number of matching rows before applying the limit. You can then execute a SELECT FOUND_ROWS() statement to retrieve the total count.

Usage:

To use SQL_CALC_FOUND_ROWS, simply add it to the beginning of your query:

<code class="sql">SELECT SQL_CALC_FOUND_ROWS ...</code>

After executing the query, run the following to retrieve the total number of rows:

<code class="sql">SELECT FOUND_ROWS();</code>

Example:

Let's say you have a query like this:

<code class="sql">SELECT * FROM `directory`
WHERE `category_id` = 12
ORDER BY `name` ASC
LIMIT 10 OFFSET 20;</code>

To calculate the total number of results, you would execute the following additional query:

<code class="sql">SELECT FOUND_ROWS();</code>

This will return the total number of rows in the directory table with category_id equal to 12, allowing you to efficiently determine the total number of pages required for pagination.

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