How to Efficiently Get Row Count in Paginated MySQL Queries with SQL_CALC_FOUND_ROWS?

Using SQL_CALC_FOUND_ROWS to Get Row Count Despite LIMIT

When executing complex MySQL queries with pagination ("LIMIT" clauses), obtaining the total row count can be challenging. Running the query twice, once without the LIMIT clause for the total count, can be inefficient.

Fortunately, MySQL introduced the SQL_CALC_FOUND_ROWS option, which allows you to calculate the total row count while still using the LIMIT clause. Here's how it works:

1. Add SQL_CALC_FOUND_ROWS to Your Main Query:
   Just after the SELECT clause, add SQL_CALC_FOUND_ROWS to your main query.
2. Execute a Second Query to Get the Row Count:
   Run a second query using the FOUND_ROWS() function to retrieve the total row count.

Example Queries:

<code class="sql">-- Main query with SQL_CALC_FOUND_ROWS
SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;

-- Second query to get row count
SELECT FOUND_ROWS();</code>

Limitations and Alternatives:

While SQL_CALC_FOUND_ROWS provides a convenient solution, note that its usage is deprecated in MySQL 8.0.17. In most cases, running the query twice is still faster.

As an alternative, MySQL 8.0 introduces the following optimized options for getting the row count:

• ROW_COUNT() Function: Returns the number of rows returned by the previous query.
• COUNT() Function: Use COUNT(*) as a subquery to calculate the row count.

By utilizing these techniques, developers can efficiently retrieve the total row count in their paginated MySQL queries without compromising speed or performance.

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