14

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too?

Currently, I use two separate queries to do so as

SELECT COUNT(*) FROM col WHERE CLAUSE
SELECT * FROM col WHERE CLAUSE LIMIT X

Is there a way to do this in one query?

EDIT: The output should be the col cells and the number of rows. In fact, after selecting the col cells, it should walk over the table to count only.

I know that it is not possible to merge these two queries, as the first return 1 row, but the second X rows. I am curious if there is a function in mysql to return the number of rows in the presence of LIMIT.

Improve this question
2
  • Could you show a line or two of your desired output?
    – dezso
    Nov 5, 2012 at 18:55
  • 1
    The two queries aren't compatible, as they're currently written: One returns a single record with a single column, the other will return many columns in X rows. Please show an example of the desired output if you want us to give a meaningful answer.
    – FrustratedWithFormsDesigner
    Nov 5, 2012 at 18:57

3 Answers 3

Reset to default
8

It sounds like you want to run the LIMIT, but still know how many rows would have been returned without the LIMIT. Check out FOUND ROWS

Improve this answer
7
  • 1
    +1'ing this for FOUND ROWS, and while it's still executing two queries, it may be better performance than running a second COUNT(*) query (depending on queries and dataset).
    – Derek Downey
    Nov 5, 2012 at 20:22
  • This is EXACTLY what I meant. I knew that mysql will not leave us alone on such an important issue ;)
    – Googlebot
    Nov 5, 2012 at 20:59
  • 1
    Glad that would help. Don't worry, mysql will still screw you on several important issues.
    – Alain Collins
    Nov 5, 2012 at 21:02
  • @DTest: or (it may be) worse performance than running a second COUNT(*) query.
    – ypercubeᵀᴹ
    Nov 5, 2012 at 21:20
  • 5
    This answer is old but the linked page says this method is "deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version." They now suggest using the original second query from the question.
    – Magnus Bull
    Jul 9, 2020 at 13:30
5

I know this is an old one but it is not valid anymore, with MySQL >= 8.0.17 cf MySQL Function Found_Rows() So we still have to issue both queries ie. add one with the COUNT(), unfortunately.

Improve this answer
3

Use SQL_CALC_FOUND_ROWS in your SELECT query and then run SELECT FOUND_ROWS():

SELECT SQL_CALC_FOUND_ROWS field_name,filed_name2 FROM col WHERE CLAUSE LIMIT X;
SELECT FOUND_ROWS() ;
Improve this answer
1
  • 1
    This would benefit from some clean up and a few more details in order for it to be more useful.
    – RLF
    Feb 11, 2016 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.