'COUNT(id) vs. COUNT(*) in MySQL

Is there a difference between the following queries, assuming there is a primary field 'id' in the table (as in speed, etc)?

SELECT COUNT(id) 
  FROM table

vs. 

SELECT COUNT(*) 
  FROM table
sqlmysqlcount


Solution 1:[1]

I know this question is about MySQL, but for what it's worth, count(*) is recommended for Oracle, which goes to show that the answer to this can be database dependent (see comment above from BalusC).

Since a lot of databases (MS-SQL, MySQL) have information schema tables that hold various types of metadata, there are bound to be differences if one syntax is simply looking up a readily-available value, and another is going straight to the table.

At the end of day: try different options, and see what EXPLAIN is telling you is going on behind the scenes.

Solution 2:[2]

One important different is that Count(*) and Count($col_name) can show different outputs if the $col_name column is nullable, since null values don't get counted by Count.

Solution 3:[3]

I know this is several years old but I don't see any evidence on which one to use, so I will post here my findings.

Executing explain in MySql Workbench for an InnoDB table on MySql 5.7 I see the following:

Executing count(*)

Executing count(id)

As you can see, both results are identical, so for this scenario both expressions are equivalent

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 zcoop98
Solution 2 zcoop98
Solution 3 Gabriel Espinoza

Related Questions