I'm part of the database team at my company, and I'm currently facing a dilemma regarding query optimization and performance. Whenever some of my colleagues come across a slow query, their default solution is to create an index to speed it up. This has resulted in some tables having more than 70 indexes!
Personally, I tend to approach this issue differently. Instead of creating new indexes each time, I often find myself modifying existing indexes, such as adding an additional included column etc., to speed up a query. However, not all team members adopt this approach, leading to an increasing number of indexes in our database.
Despite these efforts, I've noticed a slowdown in other DML operations as the number of indexes increases. It seems like a catch-22: without the index, the query is slow, but with the index, everything else slows down a little bit.
I'm looking for advice on how to navigate this situation. How many indexes are generally considered "too many"? Are there any best practices or strategies for balancing the need for fast queries with overall database performance?
Any insights or resources would be greatly appreciated.