Hi!

Following the excellent yesterday's suggestion to stop using a
particular index, I am looking into optimizing the indexes in my
database. I understand that indexes with low selectivity may hurt
performance, but the question arises, "which value of selectivity is
low?" It seems to me that I can safely get rid of indexes:

1) on tables with very few records (say up to a 100) regardless of the
number of distinct key values, because scanning the whole table probably
won't be much slower than engaging an index and using it.

2) on tables where the number of records is significantly higher than
the number of distinct key values. I am uncertain of the size of the
gap, though - it is obvious that an index on a 100 000 record table with
2 distinct values is useless, but what about a 10 distinct-values-index
on the same table? And would the index remain useless even if both the
numbers were multiplied by the same value? (e.g. 1 million records, 100
distinct values).

Correct?

Suppose that I have a table with 100 000 rows and only 2 distinct key
values. Vast majority of the records have key value 'A', just a handful
of records has 'B'. Wouldn't it make sense to keep the index if I only
ever need to find those "B" records?

Thanks, Pepak