Although having indexes can reduce the time it takes to retrieve data, they always carry a cost in maintenance and concurrency issues. When should a field be indexed? There is no strict answer for this because it depends on the type of application.
The first guideline is that fields containing highly duplicated data should not be indexed (for example, fields with the Yes/No data type, and fields that represent gender, state abbreviations, or country codes).
The second guideline is that fields should not be indexed simply to force Rushmore to use more than one index. An example of this would be indexing a field called City and a field called PostalCode in a customer table when the application is always going to be using both fields for retrieval purposes. In this instance, the PostalCode field is going to be the most unique index and would return a result set faster if the City field was not indexed. Because Rushmore doesn’t need to use the index on the City field, omitting the index on the City field will reduce overall disk I/O. Of course, if both values were not always being entered and they were used alternatively and equally, then having an index on both fields would probably be advantageous. Rushmore is best utilized on combined indexes when they are required to generate a unique result set.
It is also important to remember that indexes create concurrency issues. One index page represents many data pages. Therefore, modifying an index page can cause users with data on an entirely different data page to be locked out when trying to update the indexed field.
To see this behavior, open the Northwind database in Microsoft Access 97 and turn on pessimistic locking by clicking Edited Record on the Advanced tab of the Options dialog box (Tools menu). Update a value in one indexed field in the Customers table, but don’t move to the next record. On another workstation, open the Customers table and try to edit another value in the same indexed field that the other workstation is editing. Next, try updating a value in a non-indexed field in the Customers table. What will become evident is that substantially more records of data are locked when you try to update a value in an indexed field than when you try to update a value in a non-indexed field.