Re: Does SELECT DISTINCT on an indexed column do an index scan?

[Sergey Petrunya]

Boris,
The plan you've posted shows that a full index scan will be performed. For MySQL 4.x there is no way to avoid reading all index records for your query.
In MySQL 5.0 we've added a new optimization that allows to read only the #distinct_values_in_index tuples from the index (see http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html)

When this optimization is used EXPLAIN will look like this:
mysql> explain select distinct a from mail_item\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mail_item
type: range
possible_keys: NULL
key: a
key_len: 5
ref: NULL
rows: 6
Extra: Using index for group-by
1 row in set (0.00 sec)
Notice the "Using index for group-by" in "Extra" column.

Sergey Petrunia, Software Developer
MySQL AB, www.mysql.com
My blog: http://s.petrunia.net/blog