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)?开发者_StackOverflow

SELECT COUNT(id) 
  FROM table

vs.

SELECT COUNT(*) 
  FROM table

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.

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:

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

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.