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

JSP Performance Tips

Here are a few tips to improve JSP performance that I have been using. If you have your own listing of the tips, plz share. Disable JSP auto reloading feature. Use thread pool for your JSP engine and define the size of thread pool as per application requirement. Use jspInit() method to cache static data Initialize the 'out' object (implicit object) with proper size in the page directive. Set the content length Give 'false' value to the session in the page directive to avoid session object creation. Flush the data partly Use StringBuffer rather than using + operator when you concatenate multiple strings Use include directive instead of include action when you want to include the child page content in the translation phase. Avoid giving unnecessary scope in the 'useBean' action. Use print() method rather than println() method Use ServletOutputStream instead of JSPWriter to send binary data Minimize code in the synchronized block Do not use custom tags if you do no...
3 comments
Read more

How old are the daughters?

Two MIT math graduates bump into each other at Fairway on the upper west side. They hadn't seen each other in over 20 years. The first grad says to the second: "how have you been?" Second: "Great! I got married and I have three daughters now" First: "Really? how old are they?" Second: "Well, the product of their ages is 72, and the sum of their ages is the same as the number on that building over there.." First: "Right, ok.. oh wait.. hmmmm.., I still don't know" second: "Oh sorry, the oldest one just started to play the piano" First: "Wonderful! my oldest is the same age!" Problem: How old are the daughters?
11 comments
Read more