Getting total mysql results returned from query with a limit: FOUND_ROWS error

Question

I have this mysql query:

SELECT Files.GUID, Files.Name, Files.Type, Files.Visibility, Files.CreationDate, Files.OwnerUser, Date, BackupsCount
FROM Files
  LEFT JOIN (
    SELECT FileGUID, Date, COUNT(*) AS BackupsCount
    FROM Versions
    GROUP BY FileGUID
  ) Versions ON Files.GUID = Versions.FileGUID
WHERE Files.ParentFolder = '96251A8B-B2A8-416B-92D7-3509E6A645C7'
ORDER BY Files.Type DESC, CreationDate ASC
LIMIT 0, 50

I'd like to know how many record this query would return if the LIMIT directive was not there, I googled and found that I needed to add SELECT SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS();

MY problem is: I couldn't get it to work:

SELECT SQL_CALC_FOUND_ROWS * FROM (
SELECT Files.GUID, Files.Name, Files.Type, Files.Visibility, Files.CreationDate, Files.OwnerUser, Date, BackupsCount
FROM Files
  LEFT JOIN (
    SELECT FileGUID, Date, COUNT(*) AS BackupsCount
    FROM Versions
    GROUP BY FileGUID
  ) Versions ON Files.GUID = Versions.FileGUID
WHERE Files.ParentFolder = '96251A8B-B2A8-416B-92D7-3509E6A645C7'
ORDER BY Files.Type DESC, CreationDate ASC
LIMIT 0, 50)

-- Find total rows
SELECT FOUND_ROWS()

Assuming this is good idea (I mean doing one query vs two separate ones), how can I get the FOUND_ROWS() to work with this query?

LSerni · Accepted Answer · 2012-10-16 14:10:50Z

1

The idea is correct, and the second query is almost instantaneous. But you have to add the keyword after the SELECT, no more:

SELECT Files.GUID, Files.Name, Files.Type, ...

becomes

SELECT SQL_CALC_FOUND_ROWS Files.GUID, Files.Name, Files.Type, ...

Then the number of found rows will be populated and you'll be able to recover it.

If you want to run a single retrieval query, but you are able to execute more than one query, then you can do this (it is two queries):

SELECT COUNT(*) INTO @FOUNDROWS FROM ( YOUR_QUERY_WITHOUT_SELECT ) AS orig;

and alter your retrieval query by adding @FOUNDROWS as foundrows to the fields:

SELECT ...YOUR FIELDS..., @FOUNDROWS AS foundrows FROM...

Finally you can run a JOIN as a single query containing the two queries:

SELECT ALL_YOUR_FIELDS, counter.FOUND_ROW_NUMBER FROM
( YOUR_QUERY_WITH_LIMIT ) AS limited
JOIN ( SELECT COUNT(*) AS FOUND_ROW_NUMBER FROM YOUR_QUERY ) AS counter;

edited Oct 16, 2012 at 14:10

answered Oct 16, 2012 at 13:41

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheDude Over a year ago

Thank you, but I still need both the rows and the total count. I mean adding SELECT SQL_CALC_FOUND_ROWS didn't show the total count when I run it

LSerni Over a year ago

No, it wouldn't; it only stores the count, then you have to retrieve it in a second query. You could do it in a single query but it would cost you... I'll try and improve my answer.

Pomyk · Accepted Answer · 2012-10-16 13:37:11Z

1

The query should be like this:

SELECT SQL_CALC_FOUND_ROWS Files.GUID, Files.Name, Files.Type, Files.Visibility, Files.CreationDate, Files.OwnerUser, Date, BackupsCount
FROM Files
  LEFT JOIN (
    SELECT FileGUID, Date, COUNT(*) AS BackupsCount
    FROM Versions
    GROUP BY FileGUID
  ) Versions ON Files.GUID = Versions.FileGUID
WHERE Files.ParentFolder = '96251A8B-B2A8-416B-92D7-3509E6A645C7'
ORDER BY Files.Type DESC, CreationDate ASC
LIMIT 0, 50

answered Oct 16, 2012 at 13:37

Pomyk

3,3481 gold badge20 silver badges8 bronze badges

2 Comments

TheDude Over a year ago

Thanks, but the query still only returns the rows, while I need both the rows and the total count

Pomyk Over a year ago

You get the count by doing: SELECT FOUND_ROWS() right after the first query

CG_DEV · Accepted Answer · 2014-04-06 16:01:52Z

0

A very simple example


 "SELECT SUM(final_cost) as final_cost,
SUM(call_discount) as discount_cost,
(SELECT COUNT(*) FROM call_records WHERE transaction_type ='call' AND account_id='$account_id' AND time_of_call>='$from' AND time_of_call<='$to' ) as calls,
(SELECT COUNT(*) FROM call_records WHERE transaction_type ='text' AND account_id='$account_id' AND time_of_call>='$from' AND time_of_call<='$to' ) as texts,
(SELECT COUNT(*) FROM call_records WHERE transaction_type ='appointment' AND account_id='$account_id' AND time_of_call>='$from' AND time_of_call<='$to' ) as appts
 FROM
 call_records
 WHERE account_id='$account_id'
 AND time_of_call>='$from'
 AND time_of_call<='$to' "

answered Apr 6, 2014 at 16:01

CG_DEV

8188 silver badges7 bronze badges

Collectives™ on Stack Overflow

Getting total mysql results returned from query with a limit: FOUND_ROWS error

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related