count(1) vs count(*) - any difference?
January 19, 2007 – 12:04 pmA 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.
4 Responses to “count(1) vs count(*) - any difference?”
They are treated essentially the same. You can see below that mysql internally rewrites count(*) to count(0).
mysql> explain extended select count(*) from foo;
+—-+————-+——-+——+—————+——+———+——+——+——————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——————————+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+—-+————-+——-+——+—————+——+———+——+——+——————————+
1 row in set, 1 warning (0.03 sec)
mysql> show warnings;
+——-+——+————————————————-+
| Level | Code | Message |
+——-+——+————————————————-+
| Note | 1003 | select count(0) AS `count(*)` from `test`.`foo` |
+——-+——+————————————————-+
1 row in set (0.00 sec)
mysql> explain extended select count(1) from foo;
+—-+————-+——-+——+—————+——+———+——+——+——————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——————————+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+—-+————-+——-+——+—————+——+———+——+——+——————————+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+——-+——+————————————————-+
| Level | Code | Message |
+——-+——+————————————————-+
| Note | 1003 | select count(1) AS `count(1)` from `test`.`foo` |
+——-+——+————————————————-+
1 row in set (0.00 sec)
By Ed on Jan 19, 2007
Also, on MyISAM an internal row count is maintained which is used for COUNT……
By Kevin Burton on Jan 20, 2007
COUNT(*) is converted to COUNT(1) within the parser, therefore very early in the query execution sequence.
By Philip Stoev on Jan 21, 2007
Hi!
They are identical in MSSQL as well. There is, however, a big difference in T-SQL/MSSQL between the performance of EXISTS() and SELECT COUNT(*) to determine existence…
Cheers,
Jay
By Jay Pipes on Jan 24, 2007