How Do You Get the Total Row Count When Using `LIMIT` in MySQL Queries?

Finding Row Count Despite LIMIT in MySQL Queries

MySQL queries with the LIMIT clause allow you to restrict the number of rows returned. But what if you also need to retrieve the total number of rows that match the query?

Query with LIMIT Clause and Row Count

Consider the following query that retrieves a subset of rows from two tables using JOIN:

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

This query will return the first 10 rows that satisfy the specified conditions.

Solution Using SQL_CALC_FOUND_ROWS

To obtain the total number of rows, you can use the SQL_CALC_FOUND_ROWS function in conjunction with 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

SELECT FOUND_ROWS();

The SQL_CALC_FOUND_ROWS function sets an internal row count variable before executing the query with the LIMIT clause. The FOUND_ROWS() function then retrieves the value of this variable, representing the total number of rows that would have been returned without the LIMIT.

By using this technique, you can obtain both the limited subset of rows and the total row count without the need for an additional query.

The above is the detailed content of How Do You Get the Total Row Count When Using `LIMIT` in MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!