Index databases

In general, the fastest way to access data from a database is to use an index. Indexes increase the efficiency of finding a specific piece of data. Indexing provides an efficient and fast way to identify the data (rows) in a table.

Recommendations:

• Index every primary key and most foreign keys in the database.
• Always index audience ID fields.
• Index columns that are joined in queries.
• Index columns involved in ORDER BY and GROUP BY.
• Index columns that perform sorting operations, including UNION and DISTINCT.
• Consider indexing any attributes that are frequently referenced in SQL WHERE clauses.
• Use an index for both equality and range queries.

When you use indexing, keep the following guidelines in mind:

• Add indexes only when absolutely necessary. Indexes significantly impact INSERT, UPDATE, and DELETE performance, and they require storage.
• Avoid or remove redundant indexes. For example, two indexes that use the same or similar columns make query optimization more complicated and consume more storage.
• Carefully choose one clustered index for each table.
• Avoid indexing columns that consist of LONG character strings.