As per suggestion index must be most selective to improve the performance. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index.
Example with good selectivity:
A table having 100'000 records and one of its indexed column has 88000 distinct values, then the selectivity of this index is 88'000 / 10'0000 = 0.88.
Now I come to the point. I have one table having 1,80,000 records. Fields that are being frequently used in search criteria are:
- Search record using Name of user. Field Type :-> Not null , nvarchar(32). Unique records are 627
- Search record using Active_date. Field Type :-> DateTime ,Null. Unique records are 85627
- Search records using Current_state Field Type :-> Not Null , nvarchar(32). Unique records are only 2 that are " Pending " and " Closed ".
Currently all above fields are indexed. In terms of selectivity, case (1) and (3) are not the most selectives. What should I do with them in order to improve performance?
Thanks in advance, Sam