Define Additional Indexes

Tip

Define additional indexes on columns that are often queried.

Background

The Rules Engine will automatically generate unique indexes on all keys (primary, secondary, etc.) you defined for the various database tables. To further optimize performance, you may define additional indexes on other (combinations of) columns. In particular, adding indexes to foreign key columns may greatly enhance the performance of your application. After defining indexes in the repository, you must have them actually created in the database.

When to use

The main development effort concerning indexes is to determine where additional indexes are improving the performance. In general, the performance of your application may benefit from:

·	Indexes on columns that are often queried by the user (e.g. name, ZIP code, city).

·	Indexes on foreign key columns.

·	Indexes on columns that are often used in constraint specifications.

Always manually test if additional indexes are really effective. Use USoft BenchMark index hints as a first step in increasing performance through indexing.

You should particularly consider index hints where no Available Index is listed, the column is a Foreign Key, or there are SQL statements in the corresponding Profile that take a long time to execute.

Index hints cannot be absolute because too much depends on your database, its tuning, your network configuration and workload.

To define additional indexes:

1.	From the Definer menu bar, choose Define, Additional Indexes.

The Indexes window appears.

2.	Define the index by specifying its name, and by specifying the table on which it must be created.

3.	Click the Columns tab page. For each of the columns that is to be part of the index:

·	Type a (sequence) number to indicate its order.

·	Type the name of the column. You can also click the Column lookup button and select one from the list that appears.

·	Choose Ascending, or Descending to indicate how the data in the column is to be ordered. The default value is Ascending.

4.	Save all changes.

5.	Choose Tools, Physical Tables, Create from the Definer menu bar to actually have your indexes created in the database.

Limitations

Blindly following index hints can damage performance. Always confirm that an additional index is actually used, and that it speeds up performance without decreasing data manipulation on the same table to a degree that is unacceptable. For confirmation, perform a comparative test with BenchMark or use the database vendor's diagnostic tools.