As is the case with so many issues in software development, there are trade-offs between the number of indexes and performance. The more indexes you have on a table, the more work Advantage has to perform in the background as you modify the contents of your table. For every record that is inserted, deleted, or modified, the table’s associated indexes must be updated.

In the end, it is a balancing act. You don’t want to have too few or too many indexes. In general, you want to ensure that you have indexes to support those operations that are performed frequently, and that must be performed quickly. For those operations that are performed only occasionally, or for which performance is not an issue, additional indexes should be defined only if they do not reduce performance in other areas.

For instance, if your table has only a few indexes, and their updates at runtime is not noticeable, it won’t hurt to add an additional index for non-critical tasks. On the other hand, if your table already has many indexes, and their runtime update is a performance concern, you may not want to add additional, nonessential indexes.