What is better in MYSQL count(*) or count(1)?

Question

Related (SQL Server): Count(*) vs Count(1)

Could you please tell me what is better in performance (MySQL)? Count(*) or count(1)?

That's not a dupe. It's specifically for SQL Server, this is for MySQL. I have no idea if the _answers_ will be the same but the questions are definitely different. — paxdiablo, Mar 03 '11 at 11:16
@phil On the other hand, that question's about SQL Server, and this one is about MySQL. I know the answer is the same, but does that mean the question's the same? (@paxdiablo ..."they chorused." :) ) — Matt Gibson, Mar 03 '11 at 11:18
That may be true but people looking for questions with the `[mysql]` search term will not get that one back. The close reason is "question is a dupe", not "there's some crossover with a similar question". Voting to reopen. — paxdiablo, Mar 03 '11 at 11:21
I object to the closing on account of claiming `3 + 7` is a dupe of `sqrt(100)`, even if both answers are `10`. — RichardTheKiwi, Mar 03 '11 at 12:04

score 45 · Accepted Answer · edited Aug 26 '20 at 01:43

This is a MySQL answer.

They perform exactly the same - unless you are using MyISAM, then a special case for COUNT(*) exists. I always use COUNT(*) anyway.

https://dev.mysql.com/doc/refman/5.6/en/aggregate-functions.html#function_count

For MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:
mysql> SELECT COUNT(*) FROM student;
This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.

###EDIT Some of you may have missed the dark attempt at humour. I prefer to keep this as a non-duplicate question for any such day when MySQL will do something different to SQL Server. So I threw a vote to reopen the question (with a clearly wrong answer).

The above MyISAM optimization applies equally to

COUNT(*)
COUNT(1)
COUNT(pk-column)
COUNT(any-non-nullable-column)

So the real answer is that they are always the same.

Totally not agree with U - 600 000 rows benchmark gives: `mysql> select count(1) from users; +----------+ | count(1) | +----------+ | 596465 | +----------+ 1 row in set (5.89 sec)` `mysql> select count(*) from users; +----------+ | count(*) | +----------+ | 596465 | +----------+ 1 row in set (0.10 sec)` engine=innodb PS love practice =) — Arthur Kushman, Apr 27 '14 at 18:56
@zeusakm Now run it a third time. You've just benchmarked caching! — Schwern, Feb 14 '15 at 03:56

What is better in MYSQL count(*) or count(1)?

1 Answers1

Linked