Guidelines for Creating Indexes

Here’s a few guidelines to get started.

Indexes should almost always be built on columns used as primary keys, foreign keys, or frequently used in query join and where clauses.

Tables with frequent updates should have very carefully chosen indexes, since each additional index will dramatically reduce write speeds to the table.

Very large and infrequently updated tables will benefit from numerous indexes since they won’t be updated frequently and will give the query optimizer more options to choose from.

On the other hand, very small tables might benefit from no indexes whatsoever. The query optimizer might decide that a complete table scan is actually faster than traversing an index. So the system resources would be wasted maintaining an index that never gets utilized.

Finally, columns with a higher percentage of unique values are better candidates for indexing, over columns where most of the values are the same. The distribution of values should be taken into account when developing an index strategy for the database.