Re: Use of uninitialized value in lc

FWIW: COUNT(*) is probably better than COUNT(id). COUNT(*) checks how many rows there are, and there are sometimes efficiencies in that such as checking the high water mark. COUNT(id) checks how many ids are not null, which means every value must be checked (unless it is a not null column and the optimizer is smart enough to rewrite the query.)

Comment on Re: Use of uninitialized value in lc

Replies are listed 'Best First'.
Re^2: Use of uninitialized value in lc by FloydATC (Deacon) on Nov 09, 2014 at 18:13 UTC
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. -- FloydATC Time flies when you don't know what you're doing	[reply]
Re^3: Use of uninitialized value in lc by chacham (Prior) on Nov 09, 2014 at 18:58 UTC
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]