Except if "id" is indexed as unique (such as a primary key), in which case COUNT(id) should be slightly faster than COUNT(*). At least in theory.
Sorry, but that is wrong on three counts (no pun intended.)
- Even if id is unique, there may be NULLs. And that depends on the RDBMS, where Oracle and mysql, do not store NULLs on the INDEX, you are correct that an index full scan is enough, however, in SQL Server, DB2, and other that do store NULL, there may be a NULL on the index which needs to be checked for. In any case, COUNT(*) may give a different number as those NULLs are counted.
- The primary key is not only unique, it is also not null, which means that it and COUNT(*) will return the same number. However, this requires reading the data dictionary to find that this is indeed the PK, and then reading in all the blocks if it isn't already in memory. Though, if the optimizer is smart enough, it won't bother doing this anyway and it will rewrite it to the equivalent of COUNT(*).
- COUNT(*) only needs a number, so if statistics were taken, it just needs to check the high water mark, an efficiency not available to counting columns.
Logically, nothing can be faster than COUNT(*) (or COUNT(1), etc...).
| [reply] |
