Indexing Rules of Thumb & Index Selection Decisions

Indexing Rules of Thumb :

 

 

 

1. Index every primary key and most foreign keys in the database.
2. Attributes frequently referenced in SQL WHERE clauses are potentially
   good candidates for an index.
3. Use a B+tree index for both
   equality and range queries.
4. Choose carefully one clustered
   index for each table.
5. Avoid or remove redundant
   indexes.
6. Add indexes only when
   absolutely necessary.
7. Add or delete index columns for
   composite indexes to improve performance.
   Do not alter primary key columns.
8. Use attributes for indexes with
   caution when they are frequently updated.
9. Keep up index maintenance on a
   regular basis; drop indexes only when
   they are clearly hurting performance.
10. Avoid extremes in index cardinality and value distribution.
11. Covering indexes (index only) are useful, but often overused.
12. Use bitmap indexes for high-volume data, especially in data
    warehouses.

 Index Selection Decisions :

1. Does this table require an index or not, and if so which search
   key should I build an index on?
2. When do I need multi-attribute
   (composite) search keys, and
   which ones should I choose?
3. Should I use
   a dense or sparse index?
4. When can I use a covering
   index?
5. Should I create a clustered
   index?
6. Is an index still preferred
   when updates are taken into
   account? What are the tradeoffs between queries and updates for each index
   chosen?
7. How do I know I made the right
   indexing choice?

 

-- <Physical Database Design >