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
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
