Indexing strategy

There are two approaches to index creation: proactive and reactive. Proactive index creation involves anticipating which columns are most often used for selection, joining, grouping, and ordering. Then building indexes over those columns. In the reactive approach, indexes are created based on optimizer feedback, query implementation plan, and system performance measurements.

It is useful to initially build indexes based on the database model and applications and not any particular query. As a starting point, consider designing basic indexes based on the following criteria:

• Primary and foreign key columns based on the database model
• Commonly used local selection columns, including columns that are dependent, such as an automobile's make and model
• Commonly used join columns not considered primary or foreign key columns
• Commonly used grouping columns