Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1614
  • Last Modified:

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

Difference between count(1) and count(*).
How Using count(1) increases performance?
3 Solutions
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.
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.
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.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now