An index reduces the number of rows to be retrieved, thus reducing processing time. When a multicolumn index is used, fewer I/O accesses to the database are required than when a single index is used.

Retrieval performance is improved when an index is set on the following columns:

• Columns to be used as the condition for narrowing data
• Columns to be used for joining tables
• Columns to be used for sorting or grouping

When many rows are to be updated or when the rows to be retrieved cannot be narrowed, the benefits of defining and using an index are not realized.

In the following cases, the benefits of using an index are not realized, because the number of rows to be retrieved cannot be reduced:

• No search condition is specified
• Many of the rows have the null value or the default value in the column

Because all related indexes are updated during data addition, updating, or deletion, the number of indexes affects processing efficiency. Processing time increases and efficiency declines, unless the number of indexes is minimized to the required number. An SQL error can also occur.