How to Count Rows When Using SELECT with LIMIT in MySQL?

Counting Rows When Using SELECT with LIMIT in MySQL

In MySQL, using the LIMIT clause in a SELECT query limits the number of rows returned. However, if your query also includes complex filtering conditions, you may want to know the total number of rows that satisfy those conditions, even if they are not returned by the LIMIT.

To achieve this, MySQL provides the SQL_CALC_FOUND_ROWS modifier. Here's how to use it in your 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();

In this query:

• The first SELECT statement uses SQL_CALC_FOUND_ROWS to instruct MySQL to calculate the total number of rows matching the criteria.
• The second SELECT statement uses the FOUND_ROWS() function to retrieve the calculated row count.

This approach allows you to retrieve both a paginated result set with LIMIT and the full row count within a single database query. Note that SQL_CALC_FOUND_ROWS must be used in the same connection and before the FOUND_ROWS() function is called.

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