Table design | Avoiding deadlocks

12.2.2 Index design

Having an efficient set of indexes defined for a table will improve performance on queries that access and update information for that table. For practical reasons, you cannot have indexes on every column. This would result in extremely poor performance when rows are inserted or updated in a table. The more indexes you have, the higher the additional overhead to insert, delete, and potentially update. A good start is to define indexes on these columns:

• Columns involved in the WHERE clause of an SQL statement, especially if it is part of a join with another table.

• Columns involved in the ORDER BY or GROUP BY clause. If you have a two-column order by clause, you may want to have a compound index that includes those two columns. The leading index columns would have to exactly match the ordering in the ORDER BY or GROUP BY clause.

• Columns included in the result set. This is an added bonus because it may result in no data pages being accessed to process the query.

• The order of columns in a compound index (an index that involves more than one column) is extremely important. If the leading column of the index is not used in the WHERE clause of an SQL statement, then that index will not be selected by the optimizer to improve performance. For example, suppose the EMPLOYEE table has an index on LASTNAME + FIRSTNAME. The following query will not be able to use that index. Instead, a table scan will be performed:

  SELECT lastname, firstname, salary FROM employee WHERE FIRSTNAME = `John'

• When creating a compound index, consider placing the column with the most values first and the column with the fewest values last.

  Remember that WC queries are dynamic. The access path that you see could easily change, and usually will in a real-world application. Foresight may be more important than DB2 Explain!