Guidelines for selecting indexes

  • 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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s