- Frequency that data is retrieved from a table based upon the values of a specific column. The more often data is retrieved based on the values of a column within a table, the greater the need for an index on that column. The data may be specifically required, such as a social security number, or found in a range of values, such as order date.
- Whether the column is used to build joins with other tables. Joins are almost always guaranteed better performance when the join columns in both tables are indexed. For example, foreign key columns should have an index associated with them.
- When the data in the column is usually needed in the same order every time.
- When the values in the index leaf node can answer the query without going to the data node
- When columns have a large number of distinct values. Do not index columns that contain relatively few distinct values