Jump to content


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


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


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.



Share this post

Link to post
Share on other sites



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




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.