Count(1) vs Count(*) - Any Difference?
A while ago when I started my first job in LA, I was using MSSQL. I was told to never use count(), and rather to use count(1). Several people insisted this performs better than count(), and since I really didn’t care to argue or look into it, I just started doing it.
Fast forward to today, where I am still writing count(1), but on MySQL. I’m at a different company now, but working with a lot of the same people. The person who used to be my boss is now my equal, and while mytop was running, noticed the count(1) and was happy to see it. Which reminded me to wonder - does it even matter?
(Using a table called questions, 130K rows)
EXPLAIN SELECT count( 1 ) FROM questions;
Says it’s a simple select, and it’s using the index. Fantastic.
explain select count(*) from questions;
Same result as above. I’ve always assumed that the database engine would map count(*) as a special exception to just count all the rows, rather than actually looking at every field in the table. Since Oracle makes this (simple) calculation, I would assume the MySQL developers did as well.
If I am wrong, please let me know. If anyone knows about MSSQLs execution plans in the different versions, please leave me a comment with your results.
If you found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.