When should you Create Secondary Indexes?

How well an existing index supports data selection from a table largely depends on whether the data set selected using the index represents the data set which will ultimately be selected. This can best be shown using an example.

An index is defined on fields FIELD1, FIELD2, FIELD3 and FIELD4 of table BSPTAB in this order. Table BSPTAB is accessed with the SELECT statement:

SELECT * FROM BSPTAB WHERE FIELD1 = X1 AND FIELD2 = X2 AND FIELD4= X4

Since FIELD3 is not specified more exactly, only the index sorting up to FIELD2 is of any use. If the database system accesses the data using this index, it will quickly find all the records for which FIELD1 = X1 and FIELD2 = X2. You then have to select all the records for which FIELD4 = X4 from this set.

The order of the fields in the index is very important for the accessing speed. The first fields should be those which have constant values for a large number of selections. During selection, an index is only of use up to the first unspecified field. Only those fields which significantly restrict the set of results in a selection make sense for an index. You should create secondary indexes if you have frequent selections and the primary index does satisfy this requirement.

Additional indexes can also place a load on the system since they must be adjusted each time the table contents change. Each additional index therefore slows down the insertion of records in the table. For this reason, tables in which entries are very frequently written generally should only have a few indexes.

The following selection is frequently performed on an address file ADRTAB:

SELECT * FROM ADRTAB WHERE TITLE = ‘Prof.’ AND NAME = X AND FIRSTNAME = Y

The field TITLE rarely restricts the records specified with NAME and FIRSTNAME in rare cases in an index on NAME, FIRSTNAME and TITLE, since there are probably not many people with the same name and different titles. This would not make much sense in an index. An index on field TITLE alone would make sense for example if all professors are frequently selected.

The database system sometimes does not use a suitable index for a selection, even if there is one. The index used depends on the database optimizer. You should therefore check whether the index you created is also used in the selection (see Checking whether an Index is Used).

Creating an additional index could also have side effects on the performance. This is because an index which was used successfully for selection might not be used any longer by the optimizer if the optimizer estimates (sometimes incorrectly) that the newly created index is more selective.

The indexes on a table should therefore be as disjunct as possible, that is they should contain as few fields in common as possible. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.