MySQL Forums
Forum List  »  Optimizer & Parser

Does SELECT DISTINCT on an indexed column do an index scan?
Posted by: Boris Burtin
Date: October 31, 2005 03:45PM

I can't tell how to read this EXPLAIN plan. The optimizer says that it's using the index, but the number of rows scanned is equal to the number of rows in the table. Is getting the list of distinct values on an indexed column a fast operation? This is a table that could potentially grow to hundreds of thousands of rows, so I want to make sure that the operation is fast. So I have a large number of rows and a small number of distinct values.

Thanks in advance,



mysql> select count(distinct flags) from mail_item\G
*************************** 1. row ***************************
count(distinct flags): 17

mysql> create index i_flags on mail_item(flags);
Query OK, 6021 rows affected (1.66 sec)
Records: 6021 Duplicates: 0 Warnings: 0

mysql> explain select distinct flags from mail_item \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mail_item
type: index
possible_keys: NULL
key: i_flags
key_len: 4
ref: NULL
rows: 5348
Extra: Using index
1 row in set (0.00 sec)

Edited 1 time(s). Last edit at 10/31/2005 05:19PM by Boris Burtin.

Options: ReplyQuote

Written By
Does SELECT DISTINCT on an indexed column do an index scan?
October 31, 2005 03:45PM

Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.