When should I create database indexes? [duplicate]

Question

• When to set index for a table, during table creation or on performance tuning?
• What are the advantages and disadvantages of indexing?

Answer 1

Many (most?) DBMS use indexes to support unique constraints. Always create indexes to enforce unique constraints; they (the constraints) are crucial to the correct operation of your database.

Where you have a choice of how to create the index on multiple columns, put the column that will always be referenced in the queries ahead of other fields - usually. This is best if the leading column is also somewhat selective.

After you have the constraints necessary for uniqueness, consider those needed to enforce referential integrity. They are usually mandated by the DBMS too. Again, you cannot afford to have your database in a state of disintegrity -- it is a logical system, and if it contains fallacies, you can prove anything from it, which is not helpful.

After the uniqueness and referential integrity constraints are dealt with (indexed), then you may or may not benefit from some others. Choose carefully, and add as few extras as possible (zero is a good number). Each index slows up update operations (UPDATE, INSERT, DELETE), and uses storage space. The intention is that it should win its place by speeding up queries. However, don't forget that the optimizer has to think about each index and whether it can be useful in answering the query, so indexes also slow down the optimizer (though you'd probably be hard pressed to measure that effect).

When you do add indexes, add them on selective columns (not 'sex' containing 'M' and 'F', but maybe 'dob' containing dates of birth between 1900 and 2010, or maybe even more distinct values than that. Consider whether extra columns will help answer more queries. Some DBMS (such as DB2) provide for indexes with extra columns that are not part of the uniqueness constraint but which provide columns that are frequently used in the query. These can allow an index-only scan (one which does not need to access the table data because the needed values are all in the index).

There is much more that could be said, but this covers a lot of the territory.

• As few indexes as necessary - and preferably no extras.