Jump to content

Archived

This topic is now archived and is closed to further replies.

jeroman

Indexing and select distinct for the indexed field

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.