MySQL Forums
Forum List  »  Optimizer & Parser

Re: Does SELECT DISTINCT on an indexed column do an index scan?
Posted by: Sergey Petrunya
Date: November 01, 2005 12:56PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Does SELECT DISTINCT on an indexed column do an index scan?
4430
November 01, 2005 12:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.