Before you create indexes for a database table, consider how you will use the table. The most common operations on a table are:
Inserting, updating, and deleting rows
Retrieving rows
If you most often insert, update, and delete rows, then the fewer indexes associated with the table, the better the performance. This is because the driver must maintain the indexes as well as the database tables, thus slowing down the performance of row inserts, updates, and deletes. It may be more efficient to drop all indexes before modifying a large number of rows, and re-create the indexes after the modifications.
If you most often retrieve rows, you must look further to define the criteria for retrieving rows and create indexes to improve the performance of these retrievals. Assume you have an employee database table and you will retrieve rows based on employee name, department, or hire date. You would create three indexes—one on the dept field, one on the hire_date field, and one on the last_name field. Or perhaps, for the retrievals based on the name field, you would want an index that concatenates the last_name and the first_name fields (see "Indexing Multiple Fields" for details).
Here are a few rules to help you decide which indexes to create:
If your row retrievals are based on only one field at a time (for example, dept='D101'), create an index on these fields.
If your row retrievals are based on a combination of fields, look at the combinations.
If the comparison operator for the conditions is And (for example, city = 'Raleigh' AND state = 'NC'), then build a concatenated index on the city and state fields. This index is also useful for retrieving rows based on the city field.
If the comparison operator is OR (for example, dept = 'D101' OR hire_date > {01/30/89}), an index does not help performance. Therefore, you need not create one.
If the retrieval conditions contain both AND and OR comparison operators, you can use an index if the OR conditions are grouped. For example:
dept = 'D101' AND (hire_date > {01/30/89} OR exempt = 1)
In this case, an index on the dept field improves performance.
If the AND conditions are grouped, an index does not improve performance. For example:
(dept = 'D101' AND hire_date) > {01/30/89}) OR exempt = 1
Inserting, updating, and deleting rows