Jump to content


Photo

Indexing and select distinct for the indexed field


  • Please log in to reply
1 reply to this topic

#1 jeroman

jeroman
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 03 February 2003 - 02:14 PM

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

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 03 February 2003 - 03:19 PM

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...QL_indexes.html

P.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users