To INDEX or Not to INDEX

0. The exception to any of the below is if the cost of the index in maintenance time is greater than the benefit of the index or the size of the index is within roughly 80% the size of the data or greater in size than data in the table. 1. Primary keys should almost always be indexed. 2. Foreign keys should almost always be indexed. 3. Look through the most common executed queries and add indexes for any columns in the "where" clauses that are not covered by steps 1 & 2. 4. Look through long running queries and add indexes for any columns in the "where" clauses that are not covered by steps 1, 2 & 3. NOTE: Exception in #3 & #4 is if the comparison operator is "LIKE". Also, whether you do #3 then #4, or #4 then #3 depends mostly on how the application is being used and where the bottlenecks are. Other things to watch out for: - Try not to put "too many" indexes on a table, as this would in turn slow down inserts/updates. - I usually start with step "1" above and implement a step at a time until I stop seeing benefit, then back off a step. - Compound indexes should be used infrequently and sparingly - Clustered indexes can actually degrade performance depending on how the column is used. Most design tools automatically create a clustered index on the primary key, but this should not be taken as a clustered index is "always" a good thing. There are actually very few cases where a clustered index actually results in a benefit, more often than not it creates more overhead than it produces in benefit.