Difference between count(1) and count(*).

Posted on 2011-03-22
Last Modified: 2013-12-19
Difference between count(1) and count(*).
How Using count(1) increases performance?
Question by:sakthikumar
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 27

Expert Comment

ID: 35188382
Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.
LVL 27

Expert Comment

ID: 35188391
For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT(1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.
LVL 27

Expert Comment

ID: 35188412
The point is a count (*) needs each field in a dataset row.
count(1) instead doesn't look at the real data in the dataset.

Imagine a dataset with 1 million fields per row. Couting 10 rows would result in checking 10 million dates.
counting 10 rows of (1) results in adding 10 times the number 1.

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 28

Expert Comment

by:Naveen Kumar
ID: 35188414
if it is small table which you are querying, you might not see a real performance gain for count(1) or count(*)

Technically both are same and can produce the same output. but * when used with count(*) means count all records. count(1) or count(2) are all basically the same and will produce the same output which is just to count the number of records.
LVL 70

Accepted Solution

Qlemo earned 167 total points
ID: 35188494
That's a rumor . The days are long gone when using count(*) could introduce more work. In fact, since the number of records is counted without considering NULL, there is no difference anymore between a count(1) or count(*). Size does not matter.

However, count(column) and count(distinct column) and count(*) are all different in results and performance (distinct requires an additional sort and elimination of duplicates).

The same rumor tells people a
  select * from tbl1 where exists (select one from tbl2 where tbl1.col1 = tbl2.col1)
could make a difference compared with
  select * from tbl1 where exists (select * from tbl2 where tbl1.col1 = tbl2.col1)
That's wrong, too. Any modern Query Optimizer will disregard the column list in the subselect when used for EXISTS.
LVL 74

Assisted Solution

sdstuber earned 166 total points
ID: 35188519
It's a myth, there is no performance gain by using 1 vs *.  

Generate an explain plan for both and you'll see there is no difference between them
LVL 13

Assisted Solution

riazpk earned 167 total points
ID: 35204831

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question