Chapter 11. Tuning index designs
By now, you should understand how indexes work, and how to maintain them. But what do you do if some of your indexes are no longer hitting the perfect balance of positive and negative performance impact? It may be time to tune those things!
We’re about to embark on something that is traditionally collaboration between developers and DBAs, and we’ll potentially be modifying portions of the overall database design (specifically, the index structure). Please follow along carefully, and be sure to follow the cautions and tips that I’ll offer.
The indexes that a developer or DBA creates on a database are often put there as a best guess about what will provide the most balanced performance for the application. Keep in mind that indexes can both speed up and slow down queries, and finding the perfect balance requires a deep understanding of how the database is being used by real-world users in real-world conditions. Sometimes that “best guess” isn’t 100% on the money.
Perhaps the database contains a lot more data than anticipated, and the nature of the data means that indexes aren’t providing much of a performance boost, but they’re still slowing down data inserts and updates. Or perhaps a database has become more read-only over time, and could benefit from having more indexes. Maybe users aren’t searching for records the way someone had anticipated, and entirely different indexes would be useful.