The Dos and Don'ts of Indexing

Only index when:

• A column or set of columns contain unique values, such as the table's Primary Key. If an unique index does not exist on the primary key fields then when the primary key is created the index will also be created.
  
  
• A column is used frequently in queries.
  
  
• Any column(s) that are required for referential integrity. Any column(s) referenced by a Foreign Keys that do not have an index will have an index create automatically by the instance.
  
  
• A column is used to order a report.
  
  
• A column is used to join its table with another.
  
  
• A table has more than 200 rows.
  
  
• Use a naming convention on for the index.
  
  

Do not index when:

• Any of the above do not apply.
  
  
• Four indexes already exist for a table.
  
  
• A column contains a limited variety of values (eg: 'Y/N' or 'F/M').
  
  

It should be remembered that indexes take up space in memory. Too many indexes will inhabit more memory than the actual data. In such cases, queries will be slowed, rather than speeded, because of physical size of memory to be searched in the index.

[Note: the later engines have the onstat -P command which shows the buffer utilisation with respect to data/index ]

Indexes need to be updated as the data in the table is updated. This will slow the addition, removal and updating of the table. Also, data requires to be locked when updates on them are being executed. Similarly, the data's index entries require to be locked. The more indexes on a table, the more lock resources required.