SQL: When to Avoid Index?
I need help understanding the following.
I still don't understand why I should avoid index on small selectivity query. Thanks for the enlightment beforehand.
Consider the query:
SELECT ... FROM Orders WHERE Unpaid _Flag = 'Y'
This query has (we would hope) high selectivity, being true for a small faction of the complete history of orders. If you can count on finding a value of 'Y' for Unpaid_flag
in the query, you might want an index on that column. But if the query is part of a group that just as often searches for the unselective condition Unpaid_Flag='N', you should likely avoid the index.
In this example, the meaning of the flag is likely special to the query, driving the very purpose of the query (to find bills to send out), so you can count on finding primarily queries agains 'Y', which is the rare value.