sakthikumar
asked on
Difference between count(1) and count(*).
Difference between count(1) and count(*).
How Using count(1) increases performance?
How Using count(1) increases performance?
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.
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.