Skip to main content

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?

Comments

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?
Friday, September 2, 2005 at 5:44:00 PM GMT+5:30
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.
Friday, September 2, 2005 at 9:03:00 PM GMT+5:30
Anonymous 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).
Saturday, September 3, 2005 at 9:31:00 PM GMT+5:30
Anonymous said…
In MySQL, COUNT(*) is faster than COUNT(column_name).
Saturday, May 19, 2007 at 2:11:00 AM GMT+5:30
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...
Thursday, February 7, 2008 at 8:37:00 AM GMT+5:30
Post a Comment

Popular posts from this blog

Installing Bugzilla on Windows

Bugzilla is the Bugs/Issues Tracking Tool from The Mozilla Organization. Version 2.18 is the latest stable release. There are couple of resources which guide a User installing Bugzilla on a Unix/linux machine. However, this entry describes a way to install Bugzilla on a Windows machine (W2K to be precise). This document guides you step by step through the installation process. First, get Administrative access to the machine on which you want to install Bugzilla. It should be a simple step. Usually, Users are given Administrative rights on Windows machine. However, if you dont have, contact your Administrator. Get Bugzilla Then download the Bugzilla from http://bugzilla.org/download.html . There are two ways of gettng it - through CVS or direct downloading the tar file. Remember there are no Zip files. However, any zip utility should be able to untar the Bugzilla. I download the tar file and untarred it using WinZip. I placed the untarred 'bugzilla' directory in my c: drive. So...
33 comments
Read more

Online Hindi Radio Stations

Here is the list of Online Hindi Radio Stations. Copy the URL in your media player and enjoy !! 106.2 HUMFM - UAE ==> http://www.humfm.com/humfm.asx AajKal - Asian Network ==> http://stream.servstream.com/ViewWeb/BBCRadio_music/Event/BBCAsianNetwork_hi.asx Amrit Bani - UK ==> http://62.25.97.192/amritbani?MSWMExt=.asf ApnaRadio - USA ==> http://www.apnaradio.com/live/media24/ApnaRadio.asx Asian Gold Radio - UK ==> http://62.25.96.7/asiangold Asian Sound Radio - UK ==> http://www.vtuner.com/vtunerweb/mms/mms15278.asx BBC News ==> http://www.bbc.co.uk/worldservice/meta/tx/nb/live_news_au_nb.asx BombayBeats FM ==> http://www.1.fm/player/energybbfm32k.asx City 101.6 FM - Dubai ==> http://asx.abacast.com/arabian_radio-city-24.asx DDLive Video - India ==> http://164.100.51.209/ddlive?MSWMExt=.asf DesiSoundz - India ==> http://desisoundz.com:8000 Haagstad Radio - Holand ==> mms://81.205.146.32:21/haagstadradio kismatradio.com ==> http://www.kismatradio....
8 comments
Read more

What are the different type of JDBC drivers?

There are four types of JDBC database driver: The JDBC/ODBC bridge driver (Type 1) A piece of native C-code that translates a JDBC call to an ODBC call. Use this driver for development, not for industrial-strength application environments. Note that you have to have an ODBC database driver manager + an ODBC database driver installed on the server in addition to the JDBC/ODBC bridge. Though useful for learning JDBC and quick testing, bridging solutions are rarely appropriate for production environments. Native API partly java driver (Type 2) A piece of native C-code that translates a java JDBC call to a native database call level API. Use this driver for development and deployment. Due to its native code, this driver can only be used by Java Applications with full computer access (i.e. not Applets). Type 2 drivers generally provide the best performance, but they require the installation of native libraries on clients that need to access the database. Applications using Type 2 drivers ...
1 comment
Read more