How to Count Total Rows While Using LIMIT in MySQL?

Counting Rows with SELECT and LIMIT in MySQL

In MySQL, the LIMIT clause can be used to restrict the number of rows returned by a query. However, when using LIMIT, it can be challenging to also obtain the total number of rows in the table that satisfy the query conditions.

Consider the following query, which retrieves the first 10 rows from a join between two tables, table1 and table2, based on certain conditions (cond1, cond2, ..., condN):

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 retrieve the total number of rows that satisfy these conditions without issuing another query without LIMIT, MySQL provides the SQL_CALC_FOUND_ROWS keyword. By incorporating SQL_CALC_FOUND_ROWS into the initial query, MySQL calculates the total number of rows before applying the LIMIT clause:

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

After executing this query, the total row count can be obtained using the following separate query:

SELECT FOUND_ROWS();

By combining these two queries, you can both limit the number of rows returned and retrieve the total row count, without the need for an additional query.

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