Performance of COUNT(*) and COUNT(1)

Asked

Viewed 2,496 times

14

What’s the difference between COUNT(1) and COUNT(*) in an SQL query.

For example:

SELECT COUNT(1) FROM USUARIOS;

and 

SELECT COUNT(*) FROM USUARIOS;
  • Is there any difference of interactions within SBGD?
  • Which one would be faster?
  • It would depend on the DBMS I’m using?
sql database query performance count
  • 3

    Some say that count(1) may be faster on sql server, but I’ve never actually seen it myself. I just know that by putting * or any constant is faster than referencing column

  • In recent versions of Oracle "dá igual", https://asktom.oracle.com/pls/apex/f?p=100:11:1337911574066::NO::P11_QUESTION_ID:1156159920245

  • 4

    You’ll find in some places it’s the same thing, but I like it of that explanation.

  • 1

    For what explains here not the difference. The same goes for exists

  • In the latest and most common DBMS engines, there is no difference.

4 answers

4


Which database are you talking about? Which database? When it comes to performance, it is difficult to make definitive assertions. You can change according to the implementation, then SQL Server can give one thing and Postgresql can give another. It can change from one version to another, it can change according to the configuration of that bank or the whole system. May vary according to stored data. Performance depends on implementation detail.

If the database optimizer thinks it should be exactly the same.

Note that the syntax there is very simple. In darlings more complex (JOIN) may not even give the expected result when using COUNT(1). Otherwise the fact of having a constant in place of all fields will not differ because the count will be done on all lines that pass through the filter.

Has a excellent response on this in the OS.

3

1

The difference is simple:

  • COUNT(*) will count the number of records.
  • COUNT(column name_name) will count the number of nonnull records.

  • This answer aims to meet the demand of this question https://answall.com/q/247602/64969, but as it was written, it does not answer this current question. If you contextualize saying that there is another count, making it clear that it has relevant information on topics sufficiently close to this question, then it becomes a valid answer

0

The two selects no difference in performance.

In both cases, it counts all table values, including if the columns are null. To count the rows SQL scans the entire table (Table Scan) and then performs the operations referring to COUNT (Stream Aggregate).

Browser other questions tagged sql database query performance count

You are not signed in. Login or sign up in order to post.