List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:February 18 2003 3:14pm
Subject:Re: Slow retrieval of distinct on indexed fields
View as plain text  
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
Thread
Slow retrieval of distinct on indexed fieldsRenars Jeromans18 Feb
  • Re: Slow retrieval of distinct on indexed fieldsDan Nelson18 Feb
    • Re: Slow retrieval of distinct on indexed fields [OT?]Michael T. Babcock18 Feb
  • Re: Slow retrieval of distinct on indexed fieldsPeter Grigor18 Feb
    • Re: Slow retrieval of distinct on indexed fieldsDan Nelson18 Feb
  • Re: Slow retrieval of distinct on indexed fieldsPeter Grigor18 Feb