Friday, September 2, 2005

SELECT COUNT(*) Vs SELECT COUNT(COLUMN_NAME)

I have been assigned to prepare a checklist for my project. So, while compiling a checklist for SQLs, I suggested to use
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?

6 comments:

Anders Engström said...

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...

A

Popular Posts (Last 30 days)

This is a personal blog and I do not speak for my employer.