Creating, Enabling, and Validating Indexes

Before creating indexes, consider how the indexed items are used in your implementation. Some pros and cons are shown below:

Pros	Cons
Excellent for reads.	Write performance is impacted (data is duplication).
Query execution runtimes are significantly reduced.	More disc space is required (for duplicate data).
Table joins are significantly reduced.	Great potential for deadlock during write operations. (More data in one row as opposed to separate rows within separate tables.)
	Item order in the definition of the index is important because if the operation does not use the first item, the index is skipped.

Attributes that have associated rules cannot be included in an index.

An index must be added and then enabled before it is used to improve performance.

The process of enabling an index generally takes 0.01 seconds per object in the database. Some rough processing times that can be expected are shown in the table below:

Number of objects in DB	Time to enable an Index
500,000	1.5 hours
5,000,000	14 hours

To optimize performance, validating the tables is recommended. You should disable an index before performing bulk loads or bulk updates. Creating and enabling indexes are not appropriate actions to be performed within explicit transaction boundaries, particularly if additional operations are also attempted before a commit.

For more information, see MQL Command Reference: index Command.

Up-to-date database statistics are vital for optimal query performance. After enabling an index, generate and add statistics to the new database tables. This is assuming statistics are already up-to-date for all other tables.