33

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
James Simpson
  • 13,488
  • 26
  • 83
  • 108
  • 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 – Rakesh Juyal Nov 08 '09 at 17:02
  • Have a look at [Count(*) vs Count(col)](https://www.percona.com/blog/2007/04/10/count-vs-countcol/) 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. – Pascal Thivent Nov 08 '09 at 17:02

3 Answers3

4

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.

zcoop98
  • 2,590
  • 1
  • 18
  • 31
davek
  • 22,499
  • 9
  • 75
  • 95
  • 1
    It seems to me more and more that the way to go is either to use an ORM (and hope it optimizes for all cases), or to write SQL for exactly *one* database engine and forget about the rest. – Camilo Martin Dec 18 '13 at 08:54
2

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

Gabriel Espinoza
  • 385
  • 1
  • 18
1

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.

zcoop98
  • 2,590
  • 1
  • 18
  • 31
Guguzz
  • 11
  • 1