sql count performance

Question

Possible Duplicate:
Count(*) vs Count(1)

If I have a table, 'id' is primary key, then these two commands have different performance ?

select count(*) from t;
select count(id) from t;

thanks

What does the query optimizer tell you? – Jun 04 '12 at 17:49 — , Jun 04 '12 at 17:49

score 0 · Answer 1 · answered Jun 04 '12 at 17:51

0

No, Oracle takes over and takes the fastest way in case of count(*)

answered Jun 04 '12 at 17:51

Sebas

21,192
9
55
109

score 0 · Accepted Answer · answered Jun 04 '12 at 17:52

These would have the same performance. In most databases, count() results in a scan of the table or available indexes. Whether or not it uses the index instead of the table depends only on the query optimizer. If the optimizer is smart enough to use the index, it should be smart enough in both cases.

Using available metadata tables, you can often get the number of rows in a table much mroe efficiently than by using a count() query.

score 0 · Answer 3 · answered Jun 04 '12 at 17:59

0

I think if id is primary Key both count(*) and count(id) are semantically equivalent.

But for readers count(id) means the intention to count all rows where id is not null. To avoid confusions I would rather use count(*).

answered Jun 04 '12 at 17:59

alexm

6,854
20
24

sql count performance

3 Answers3