Friday, January 21, 2011

A Quick Start Guide to Index Optimisation


Below a Quick Start Guide to index optimisation, courtesy of Pavel Cisar.

1. Always define ALL integrity constraints: primary, foreign and unique constraints. As this will automatically cover join optimisations.

2. Define a separate index on EACH column you will use for additional constraints in your queries (eg. WHERE clause filter conditions) that are not covered by point 1.

3. Update your index statistics regularly as you add/change/delete data in your database. A good general rule is to update statistics as part of your regular database maintenance routine (when you do a backup, sweep etc.), and/or whenever you add/change a quarter of all the rows, once the table is bigger than 10,000 rows.

4. Once you have a representative sample of real world data in your database, you can then evaluate the usability of the indices to eliminate those that will not help your queries (a worthless index will only slow down your inserts and updates!) and add new composite or expression indices that speed up specific queries.

5. Useless indices are typically those with low selectivity (few distinct values). Run some queries with filter condition on bad cardinality columns and check whether the optimizer uses the index or not. If the low selectivity index is always/often used with other conditions, you can improve it's selectivity and thus usability by
creating a composite index on columns that are used together for filter conditions instead of independent indices on these columns.

6. If you always/frequently use a group of columns for filter conditions, a composite index on these columns can boost the query performance, but do it only if you're not satisfied with performance that individual column indices provide.