SQL getting row count optimization

Question

Possible Duplicate:
In SQL, what's the difference between count(column) and count()?
Count() vs Count(1)

I have big tables which keep long texts for example email content or news. And my question is there any difference for performance for counting table rows :

SELECT COUNT(*) FROM table_name

SELECT COUNT(t.id) FROM table_name as t

Which one is better? or the latter one will be optimized by query optimizer? Is there any documentation regarding this?

Assuming `id` is a non nullable column (looks like the PK). They should be the same in any decent DBMS — Martin Smith, Jan 13 '12 at 15:19
@juergend - COUNT(*) and COUNT(1) are synonyms. But COUNT(id) has different behavior. — MatBailie, Jan 13 '12 at 15:23

score 2 · Accepted Answer · answered Jan 13 '12 at 15:23

2

They are different.

COUNT(*) will retrieve all values (even the NULL values count). COUNT(t.id) doesn't count the NULL values.

In terms of performance, they are the same (the query optimizer is smart).

answered Jan 13 '12 at 15:23

aF.

64,980
43
135
198

1

In terms of performance they are only categorically the same for non nullable columns. If the column is not nullable then the QO can choose a narrower index that does not even contain the specified column or (for MySQL - can't remember which storage engine) use the cached value that does not require a table access at all. – Martin Smith Jan 13 '12 at 15:24

score 1 · Answer 2 · answered Jan 13 '12 at 15:26

1

Query plan looks the same, but in testing, using * is faster (slightly).

Nulls are not taking into account when specifying a column name though.

answered Jan 13 '12 at 15:26

UnhandledExcepSean

12,504
2
35
51

SQL getting row count optimization

2 Answers2

Linked