I have been assigned to prepare a checklist for my project. So, while compiling a checklist for SQLs, I suggested to use
[COLUMN_NAME is NOTNULL column] instead of
My impression was that while for COUNT(*), DB has to do a complete scan of the table, for COUNT(COLUMN_NAME), it require to scan the COLUMN only. But then, I dont know why, I just decided to google it. And I came across this AskTom Entry where it says it is just a myth that COUNT(COLUMN_NAME) is faster than COUNT(*).

I dont know how different DBs behave in this matter?

What u guys think of it? What are ur experiences?


According to an Oracle consultant, SELECT COUNT(1) FROM TABLE is the fastest way to count records (in Oracle 9i that is).
Rajneesh Garg said…
If u go thro the entry, u would find it also talk of SELECT COUNT(1) and SELECT COUNT(ROW_ID) as well :)
Do u have an openion on Databases other than Oracle?
Fabien said…
SELECT COUNT(*) used to be much faster in hsqldb, although now they have fixed that. Those kind of optimizations can I think only be considered after appropriate testing. It varies from DB vendor to DB vendor and from version to version.
Dion Almaer said…
If select count(1) vs. count(*) is your performance bottleneck, you are very lucky indeed :)

(of course, you don't want to do something "slower" if you know of a better way).
Anonymous said…
In MySQL, COUNT(*) is faster than COUNT(column_name).
Anonymous said…
in SQL Server 2005, it used to be the same, but when i tried it with 10 million records, COUNT(column_name) is faster by 1 second. weird...

Popular posts from this blog

Installing Bugzilla on Windows

Online Hindi Radio Stations

Copy/Paste the block of text in vi/vim