SELECT COUNT(COLUMN_NAME) FROM TABLE
[COLUMN_NAME is NOTNULL column] instead of SELECT COUNT(*) FROM TABLE
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?
5 comments:
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?
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.
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).
In MySQL, COUNT(*) is faster than COUNT(column_name).
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...
Post a Comment