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

Question

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

Answer 1

Not in MySQL, no, it has optimized the COUNT well (assuming that your id field is already a PK --which is fairly self-describing). But in for example PostgreSQL the COUNT(pkcolumnname) is much faster.

Answer 2

Have a look at Count(*) vs Count(col) at www.mysqlperformanceblog.com, they discuss this topic for various 'col' types (NOT NULL or not, with index, etc) and this for MyISAM and InnoDB tables.

Answer 3

You can look onto these threads

http://stackoverflow.com/questions/1221559/count-vs-count1

http://stackoverflow.com/questions/433913/in-sql-is-there-a-difference-between-count-and-countfieldname

http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count

Answer 4

I know the question is about MySQL, but for what it's worth, count(*) is recommended for Oracle: which goes to show that this is database specific (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, an see what EXPLAIN is telling you is going on behind the scenes.

Answer 5

The difference depends on how count() is implemented in your database engine. For naive approaches, select count(*) will build a result set containing every column from every row in the result set, count those, and return the result. In such a case, using count(id) will reduce this memory and performance overhead to retrieving a single, small column instead of a structure the width of the source table(s).

An optimized approach such as that used by MS SQL will recognize the count() construct and just count the rows without building an intermediate result buffer, so count() is roughly equivalent to count(id).