Adam wrote:

>>
>> ...so do I need to index that field
>> because I have only 3-5 different values and I often use this field in
>> my sql statements like very important in where clause?
>>
>
> Usually such poor selectivity will make performance worse. Indices can
> only be of benefit if they eliminate the need to read data pages from
> disk. When there are so many duplicates, the chances of being able to
> ignore an entire data page is reduced to near 0.
>

In this case, the index may not be so bad. Records in one year
tend to be stored together, just because they tend to be created
in the year that's in the field. If that's the case, the index
will take you to the 20-30% of the pages that have relevant records.

Similarly, if you have records of breast cancer patients, an index
on gender is very useful when you're looking for males, and much
less so when looking for females. As far as I know, an index on
gender is no use in a database of testicular cancer patients.


Cheers,

Ann