Should I COUNT(*) or not?

Question

I know it's generally a bad idea to do queries like this:

SELECT * FROM `group_relations` 

But when I just want the count, should I go for this query since that allows the table to change but still yields the same results.

SELECT COUNT(*) FROM `group_relations` 

Or the more specfic

SELECT COUNT(`group_id`) FROM `group_relations` 

I have a feeling the latter could potentially be faster, but are there any other things to consider?

Update: I am using InnoDB in this case, sorry for not being more specific.

Answer 1

If the column in question is NOT NULL, both of your queries are equivalent. When group_id contains null values,

select count(*) 

will count all rows, whereas

select count(group_id) 

will only count the rows where group_id is not null.

Also, some database systems, like MySQL employ an optimization when you ask for count(*) which makes such queries a bit faster than the specific one.

Personally, when just counting, I'm doing count(*) to be on the safe side with the nulls.

Answer 2

If I remember it right, in MYSQL COUNT(*) counts all rows, whereas COUNT(column_name) counts only the rows that have a non-NULL value in the given column.