7

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.

  • Could you show a line or two of your desired output? – dezso Nov 5 '12 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 '12 at 18:57
4

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

  • 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 '12 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 '12 at 20:59
  • 1
    Glad that would help. Don't worry, mysql will still screw you on several important issues. – Alain Collins Nov 5 '12 at 21:02
  • @DTest: or (it may be) worse performance than running a second COUNT(*) query. – ypercubeᵀᴹ Nov 5 '12 at 21:20
  • @ypercube precisely – Derek Downey Nov 5 '12 at 21:25
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() ;
  • 1
    This would benefit from some clean up and a few more details in order for it to be more useful. – RLF Feb 11 '16 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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