There is a misconception in application development community that performance related database issues can be attributed to lack of indexes. This thinking leads to creation of indexes whether they are required or not. A comprehensive database performance management strategy is required to address performance related issues.
Creating more indexes than necessary or overindexation leads to performance problems. Yes, rather than solving the issue, having more indexes can slow your application. Some pointers to consider…
- Indexes on small tables with less than 1000 rows add no value
- Indexes are useful when searches are for keys which belong to a small minority.
- Concatenated indexes should be concatenated from the most selective to the least selective column
- Indexes on single columns which also appear as the first column in a concatenated index are redundant. Databases engines can use the concatenated index when only the first columns in the index appears in the search condition.
- When more than 10% of rows are returned, a full table scan is often as fast if not faster than an index search.
