How to Count Rows with LIMIT in MySQL Queries?

Counting Rows in MySQL Queries with LIMIT

When running MySQL queries with the LIMIT clause, users often face the challenge of simultaneously obtaining the total number of rows matching the query conditions. This arises due to LIMIT's constraint of returning only a specific number of rows.

To overcome this limitation, a technique known as the subquery method can be employed. To achieve this, incorporate the following syntax into your query:

SELECT SQL_CALC_FOUND_ROWS ...;

This statement initiates the counting of rows at the SQL level, storing the count in a temporary variable.

To retrieve the count, run a subsequent query:

SELECT FOUND_ROWS();

For instance, consider the following query:

SELECT A.ID, A.NAME, B.ID, B.NAME
FROM table1 A
JOIN table2 B ON ( A.ID = B.TABLE1_ID )
WHERE
  cond1, cond2, ..., condN
LIMIT 10

To obtain both the filtered rows and the total count, use the following query:

SELECT SQL_CALC_FOUND_ROWS A.ID, A.NAME, B.ID, B.NAME
FROM table1 A
JOIN table2 B ON ( A.ID = B.TABLE1_ID )
WHERE
  cond1, cond2, ..., condN
LIMIT 10;

SELECT FOUND_ROWS();

This technique empowers users to efficiently count rows in queries using the LIMIT clause without resorting to additional queries.

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