is index required on distinct(count(ColumnName)) with index on where clause?

Question

I have a table like this

Subscribers
  Id (PK, int)
  OwnedBy (FK, int)
  EmailAddress (varchar 50)

If I query like this:

SELECT COUNT(DISTINCT(EmailAddress))
FROM Subscribers
WHERE Subscribers.OwnedBy = 67;

There is an index on the OwnedBy column. Assuming the table is very large, should I also index EmailAddress? I am not concerned about write performance degrading.

score 1 · Answer 1 · answered Jul 20 '12 at 08:02

You should create covering index to make it even faster.

ALTER TABLE Subscribers ADD KET ix1(OwnedBy, EmailAddress);

Check status as Using where Using Index with this query:

EXPLAIN SELECT COUNT(DISTINCT(EmailAddress))
FROM Subscribers
WHERE Subscribers.OwnedBy = 67;

score 1 · Accepted Answer · answered Jul 20 '12 at 08:12

1

Yes you should.

Since your query can be rewritten like this:

SELECT COUNT(*)
FROM Subscribers
WHERE Subscribers.OwnedBy = 67
GROUP BY EmailAddress

you should have a look at the MySQL GROUP BY performance optimization pages:

http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html

There, the following is stated:

The most efficient way to process GROUP BY is when an index is used to 
directly retrieve the grouping columns

So if you are not concerned about insert/update performance and size of the table you should definitely go with an index on EmailAddress

answered Jul 20 '12 at 08:12

Horen

11,184
11
71
113

Thank you. Can you comment on your solution versus creating a covering index as mentioned by Omesh? – Harper Jul 20 '12 at 08:19
Maybe this will help you http://stackoverflow.com/questions/8213235/mysql-covering-vs-composite-vs-column-index – Horen Jul 20 '12 at 08:27

is index required on distinct(count(ColumnName)) with index on where clause?

2 Answers2