4

I'm using MySQL and I've read several times that COUNT(*) is generally faster than COUNT(Column).

However, I need to get the COUNT of distinct rows. From what I understand, I can do this in 2 different ways:

a) SELECT COUNT(DISTINCT Column) ...

or

b) SELECT COUNT(*) ... GROUP BY Column

In general, which is faster? If it depends, then what does it depend on?

ProgrammerGirl
  • 3,157
  • 7
  • 45
  • 82
  • I believe that you should have a look at `SQL_CALC_FOUND_ROWS` and stop using `COUNT()` unless really needed. – Peter Jan 09 '13 at 20:40
  • @Peter need not be. See http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count – nawfal Jan 09 '13 at 20:42
  • @nawfal I'm surprised. But keep in mind that it's a question from 2008... things could have changed a lot. And always use SQL_NO_CACHE for benchmarking – Peter Jan 09 '13 at 20:46
  • 1
    @Peter The comment confirming the answer is from Jul 11, 2012. – svidgen Jan 09 '13 at 20:50

1 Answers1

6

SELECT COUNT(*) FROM table GROUP BY column returns the number of rows for each value of the grouped by column, not the number of groups.

SELECT COUNT(DISTINCT column) FROM table returns the number of groups (although you can also get this using the row count from the GROUP BY query).

Neil
  • 54,642
  • 8
  • 60
  • 72
  • Could you please provide an example of the difference between those two, as the descriptions are somewhat confusing to a non-expert such as myself. Thanks! – ProgrammerGirl Jan 14 '13 at 13:10
  • 2
    @Programmer If `SELECT column FROM table` returns `5, 6, 5, 7, 5, 6, 5` then `SELECT COUNT(*) FROM table GROUP BY column` returns `4, 2, 1` (or variant thereof) while `SELECT COUNT(DISTINCT column) FROM table` returns `3`. – Neil Jan 16 '13 at 20:56