Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
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  
+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
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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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