Indexing and select distinct for the indexed field

[jeroman]

Hello everyone,

 

Let us assume we have a table

 

create table T (id int unsigned primary key auto_increment, php int);

create index i_php on T(php);

 

Now let us insert 2 million rows into this table with php being numbers from 1 to 5. Thus we have just 5 distinct values.

 

And now the problem: when I execute one of the following

 

select distinct php from T

or

select php from T group by php

 

it takes about 2 - 3 secs on my system to return the 5 indexed values but it must have taken just fractions of a second to lookup index and return 5 values. Where is the problem? Can anyone share his/her knowledge of how to make it read directly the indexed values?

 

Any comments are welcome.

 

Thanx

[pallevillesen]

Hi,

 

I don\'t think you\'re right, since your query still have to look through the entire index.

 

But a:

 

select id where php = \'5\';

 

would be about 1/5 of the normal time, since the index would be used. You should probably have another coloumn \'php2\' widht random values from 1 to 5 and then see the speed difference to find specific rows, i.e.

 

1. where php = \'5\' would be a LOT faster than

2. where php2 = \'5\' because php2 wasn\'t indexed.

 

Maybe I\'m wrong - I\'m not strong on indexes - but look around in

http://www.mysql.com/doc/en/MySQL_indexes.html

 

P.