Take the 2-minute tour ×

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


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.

share|improve this question
Could you show a line or two of your desired output? –  dezso Nov 5 '12 at 18:55
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
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
+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 ;) –  All Nov 5 '12 at 20:59
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
show 1 more comment

Your Answer


