From: Dan Nelson
Date: February 18 2003 3:14pm
Subject: Re: Slow retrieval of distinct on indexed fields
List-Archive: http://lists.mysql.com/mysql/132696
Message-Id: <20030218151446.GA90978@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Feb 18), Renars Jeromans said:
> This is not an urgent problem, but it has always intrigued me... It's
> simplified case, but it makes the point. Let us assume that we have a
> table
> 
> create table T (id unsigned int unique, name char(10));
> create index i_name on T(name);
> 
> Let's insert into it say 5 mil rows with name field being just say 5
> distinct values AAA, BBB, CCC, DDD, EEE.
> 
> Now the question. Why a query like
> 
> select distinct name from T;
> 
> takes about 3 sec to return just a bunch of rows? As I understand it,
> index contains all these 5 values, so just simple lookup into index
> should take fractions of a second. Can anyone comment on this? MySQL
> team?

You need to walk the entire index to make sure you have all the values.
There might be a single "AAB" inbetween those million "AAA"'s and
million "BBB"'s.

It requires a full index scan, which is usually a lot faster than a
full table scan, but it is still not instantaneous.  In your example it
won't be any quicker since your table only has two rows, but in the
usual case where a row may be a couple hundred bytes long there is a
bigger difference.

-- 
	Dan Nelson
	dnelson@stripped