What are some best practises and “rules of thumb” for creating database indexes?

Some of my rules of thumb:

• Index ALL primary keys (I think most the RDBMS do this when creating it).
• Index ALL foreign keys columns.
• Create more indexes ONLY if:
  • Queries are slow.
  • You know the data volume are going to increase significantly.
• Run statistics when populating a lot of data on tables.

If a query is slow, look for the execution plan and:

• If the query for a table only uses few columns put all that columns on an index, then you can help the RDBMS to use only the index.
• Don't waste resources indexing tiny tables (hundreds of records).
• Index multiple columns in order from high cardinality to less. It means, first the columns with more distinct values followed by columns with few distinct values.
• If a query needs to access more that 10% of the data, normaly is better a full scan that an index.

http://stackoverflow.com/questions/687986/what-are-some-best-practises-and-rules-of-thumb-for-creating-database-indexes