SaltLake SaltLake - 5 months ago 18
MySQL Question

How to count all rows when using SELECT with LIMIT in MySQL query?

I've got a mysql query like this:

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


I've got many where clauses in query.
How to improve this query to get also full row count?
I don't want to use one more request without LIMIT.

Answer

What you are looking for is this

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