Creating Indexes

After running the Health Check Tool, you may need to add new indexes to some tables or make changes to those you already have.

The recommendations in this topic are specific to that situation. For general instructions on how to add indexes, see Define Database Properties for a Business Object.

Combining Multiple Rows

To combine multiple rows in an index:

Identify any tables that have values in EqualityUsage or InequalityUsage. The indexes recommended by those rows could be good candidates for adding an index. In the example above that would be the fourth and fifth rows; with one index you also aggregate the cost of both rows.

About Key Columns and Include Columns

The columns in the EqualityUsage indicate columns being used in queries with equality predicates ("Select * from employee where id = 2") and the InequalityUsage column displays columns being used in queries using inequality predicates, for example, "Select * from employee where id > 2").

An Index key column is the column used in the index, and used by the optimizer to decide if the index is applicable. It is also used by the execution engine to fulfill the WHERE part of the query. It is used for the SELECT part of the query if the column is needed.

An Included column is added to the index, not used by the optimizer or execution engine in finding the rows or joining tables. It can only be used by the SELECT part of the query to return the data.

What Not to Do

Adding the recommended Include columns can help speed up the query by covering the query. However, the more Include columns are added, especially when they are key columns, the longer it can take to keep the indexes up to date.

An index must be updated every time a relevant record's data is changed. When users access a record, the Last Modified date/time is highly likely to be updated. Consequently, if a record is likely to be frequently modified, we recommend that you avoid adding indexes on the Last Modified date/time field. Instead, consider adding indexes on the Created date/time field.