How to Count Rows Beyond LIMIT in MySQL Queries?

Counting Rows Beyond LIMIT in MySQL Queries

Problem:

When executing a MySQL query with a LIMIT clause, it can be challenging to obtain the total number of rows in the table that satisfy the query conditions. This is particularly true when using complex JOIN statements and WHERE clauses.

Solution:

To address this issue, MySQL provides the SQL_CALC_FOUND_ROWS keyword. When this keyword is used, it instructs MySQL to calculate the total number of rows that would have been retrieved without the LIMIT clause.

Example:

Consider the following MySQL 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 the total number of rows without the LIMIT clause, execute the following two statements in the same order:

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();

The first statement calculates the total rows satisfying the query conditions, while the second statement retrieves the total count from the FOUND_ROWS() function.

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