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

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 2

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 3

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