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

Question

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 1

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

Answer 2

You can use the SQL_CALC_FOUND_ROWS with FOUND_ROWS() to count the number of results while that query is executing. Basically you just add 'SQL_CALC_FOUND_ROWS' after 'SELECT' and then run another query 'SELECT FOUND_ROWS()' after that. It is not possible to send back the count in the same query because it cannot know the count until the query is finished.

Answer 3

'tis 4 years since the last answer, but this is how I resolved the problem. Although SaltLake's answer produced an error for me, it did lead me to the correct answer.

SELECT SQL_CALC_FOUND_ROWS * FROM wholedatabase LIMIT 0,10 UNION 
SELECT 'TotalRows', FOUND_ROWS(), NULL, NULL, NULL, NULL
ORDER BY IssueDate, VolumeNo 

The UNION part is very important, because it tags your desired answer (Total number of rows) that is retrieved in the SECOND Select result onto the FIRST Select results.

Another very important point is that, because a UNION is taking place, both tables must have the same number of columns in them. This usually means that you have to pad the SECOND Select with the all-important FOUND_ROWS() value, and then lots of NULL values.

The final result will be one command that will return 11 rows of information, with one of these rows containing the total number of rows. Obviously, you will need to exclude the additional TotalRows row when you come to using the result.

Answer 4

Solution from http://is.php.net/manual/en/function.mysql-num-rows.php#83647

SELECT SQL_CALC_FOUND_ROWS 
    '0', z.id
FROM 
    zoom AS z 
LIMIT 0,6 
UNION 
  SELECT 
    '1', FOUND_ROWS() 
ORDER BY `0` DESC , RAND()

Answer 5

You should use

SELECT SQL_CALC_FOUND_ROWS A.ID, A.NAME, B.ID, B.NAME, FOUND_ROWS() as rCount
FROM table1 A
JOIN table2 B ON ( A.ID = B.TABLE1_ID )
WHERE
    cond1, cond2, ..., condN
LIMIT 10