Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

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

like image 813
Tom Smykowski Avatar asked Mar 03 '11 11:03

Tom Smykowski


People also ask

Why count 1 is faster than count (*)?

According to this theory, COUNT(*) takes all columns to count rows and COUNT(1) counts using the first column: Primary Key. Thanks to that, COUNT(1) is able to use index to count rows and it's much faster.

Is count (*) The same as count 1?

The difference is simple: COUNT(*) counts the number of rows produced by the query, whereas COUNT(1) counts the number of 1 values. Note that when you include a literal such as a number or a string in a query, this literal is "appended" or attached to every row that is produced by the FROM clause.

Is Count ID faster than count *?

Your use of COUNT(*) or COUNT(column) should be based on the desired output only. ... if you have a non-nullable column such as ID, then count(ID) will significantly improve performance over count(*). The two seem to contradict each other.

Why do we use count 1 in SQL?

With COUNT(1), there is a misconception that it counts records from the first column. What COUNT(1) really does is that it replaces all the records you get from query result with the value 1 and then counts the rows meaning it even replaces a NULL with 1 meaning it takes NULLs into consideration while counting.


1 Answers

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.

like image 126
RichardTheKiwi Avatar answered Oct 14 '22 15:10

RichardTheKiwi