Although indexes usually provide performance gains in the retrieval of specific row or set of rows, there can be costs associated with adding indexes to a table.
For instance:
- Slower create and delete operations on rows - This is because index entries will be added or deleted when rows are added or deleted. The more indexes you have, the more index entries will have to be added or deleted.
- Slower updates of index columns - This is because the index entries must be updated whenever a column that is a component of an index is updated with a new value.
- Additional storage space - This is because the index entries will occupy additional disk space.
- Additional administration and maintenance - This is because the increase in number of indexes will increase the time to rebuild indexes. In addition, the increase in size of the database will result in more time to manage the functions such as backup and restore.
- Side-effects in ABL or SQL applications - When indexes are added to a table, code that uses that table should be analyzed for the effects of the indexing changes. This can include but is not limited to the following:
- Compile before and after using the XREF option and compare the output to see differences in indexes being used by the application. You could end up significantly slowing a program down if it is now performing a whole-index search after adding a simpler (fewer fields) index.
- Compile with the LISTING option and compare the output to see potential differences in scope that may have occurred due to the indexing change.
- In any event, adding indexes indicates a need to fully test code that uses the affected table(s) in order to ensure it is functioning as originally designed.
