mysql distinct on 6 million rows takes 17 minutes to run? Can I speed this up?

Question

I'm trying to get distinct id's out of a 6 million row table. The query is pretty simple, and the explain seems ok. The distict row is indexed as part of a grouped index of uid-date-time.

The query is

SELECT DISTINCT uid FROM events;

and returns 334117 rows in 17 min 15.54 seconds. The explain is

+----+-------------+-------------+-------+---------------+----------------+----
----+------+------+--------------------------+
| id | select_type | table       | type  | possible_keys | key            | keylen | ref  | rows | Extra                    |
+----+-------------+-------------+-------+---------------+----------------+--------+------+------+--------------------------+
|  1 | SIMPLE      | events     | range | NULL          | dateuid_idx      | 4     | NULL |   10 | Using index for group-by |
+----+-------------+-------------+-------+---------------+----------------+----
----+------+------+--------------------------+

would this be faster if the uid where held in a seperate index? or is there another way to speed this up?

Answer 1

It looks like your index in by Date and then UID which doesn't help. Indexing by UID first or having a separate index for UID will improve performance greatly.

In addition, you might consider using GROUP BY instead of DISTINCT.

Answer 2

create an index on uid

Answer 3

Yes, having uid as separate index can be much faster. Without an index a full-table scan is required; that is, it actually iterates through each row and checks if the specific uid is already selected or not.

However with an uid being an index, it only has to traverse through the index tree, which can be a lot faster if there's a lot of duplicate uids.

Answer 4

A separate uid index would speed things up. Creating the index however would probably take in th order of 17 minutes (since roughly the same amount of "reading" must be done to create teh index.

Therefore creating such an index all depends on expected usage. May be a good "investment" if relevant queries are run a few times (if only during this current "campaign" of duplicate record hunting).