How much does an index need to narrow the results of a search in order to be useful in speeding up queries?
Some examples all across the spectrum:
- A column for storing true/false values obviously has only two unique values.
- A 'last name' column probably has many unique values (although it may not).
- A primary key column has all unique values.
I think that the goal of an index is to quickly narrow a search to a few rows, and that therefore, the last case is the best, the second is OK, and the first is useless.
Am I correct? If so, roughly where is the line of usefulness? For example, if an index can narrow the results to 1% of the rows, is that useful? What about 10% or 25%?