-3

Possible Duplicate:
COUNT() vs. COUNT(1) vs. COUNT(pk): which is better?
count() and count(column_name), what's the diff?
count(*) vs count(column-name) - which is more correct?

The benefit of using count(*) in a select statement is that I can use it with any table and that makes automating scripts easier:

count_sql = 'select count(*) ' + getRestOfSQL('tablename');

But, is it less efficient than using count(specific_field)?

Community
  • 1
  • 1
dkinzer
  • 32,179
  • 12
  • 66
  • 85
  • http://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better http://stackoverflow.com/questions/1221559/count-vs-count1 – Kermit Aug 17 '12 at 19:54
  • What is `getRestOfSQL`? Can you give an example of a complete SQL query? – Mark Byers Aug 17 '12 at 19:57
  • Not a duplicate IMO. Different databases do have different performance on different forms of `COUNT` with and without where-clauses and in different conditions of nullability and where indices are (I don't have any info for an answer, but I do know the answer is different in SqlServer and PostgreSQL which I do use). While the semantic difference of `count(*)` vs `count(field_name)` is the most important thing, it's valid to wonder about this in the case of a specific db. – Jon Hanna Aug 17 '12 at 20:01
  • @JonHanna - The link I gave (also answered by @Mark) is tagged MySQL – Martin Smith Aug 17 '12 at 20:01
  • @MartinSmith Right you are. I hadn't refreshed since following njk's links. – Jon Hanna Aug 17 '12 at 20:02

2 Answers2

3

For InnoDB

If specific_field is not nullable, they are equivalent and have the same performance.

If specific_field is nullable, they don't do the same thing. COUNT(specific_field) counts the rows which have a not null value of specific_field. This requires looking at the value of specific_field for each row. COUNT(*) simply counts the number of rows and in this case can be faster as it does not require examining the value of specific_field.

For MyISAM

There is a special optimization for the following so that it does not even need to fetch all rows:

SELECT COUNT(*) FROM yourtable
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
2

Generally, it wouldn't matter so much, as we're returning the same number of rows.

This link covers it nicely

This link also explains more, specifically with Oracle

Community
  • 1
  • 1
Caffeinated
  • 11,982
  • 40
  • 122
  • 216