Organizational Research By

Surprising Reserch Topic

how to count all rows when using select with limit in mysql query


how to count all rows when using select with limit in mysql query  using -'mysql'

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.
    

asked Sep 16, 2015 by vijaygupta1980
0 votes
5 views



Related Hot Questions

4 Answers

0 votes

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();
answered Sep 16, 2015 by patelnikul321
0 votes

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.

answered Sep 16, 2015 by deven.bendale
0 votes

'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.

answered Sep 16, 2015 by ashish singh
0 votes

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()
answered Sep 16, 2015 by manju bhargava

...