Database XP / Operating

Indexes are a trade, not an improvement

Every one is paid for on every write.

Adding an index to fix a slow query is a reflex and it has a cost that is invisible in the query it fixed. Every insert, update and delete on the table maintains it, storage grows, and backups take longer. On a write-heavy table an accumulation of indexes can cost more than the reads they serve.

The other cost is redundancy. Indexes added over years by different people frequently overlap, with one being a prefix of another, and the narrower one is doing nothing while still being maintained.

Most databases report index usage, and the report usually shows several that have never been used since the last restart. Those are pure cost and removing them is one of the cheapest performance improvements available, though it is worth confirming against a period that includes the monthly and quarterly jobs.

Before adding, it is worth checking whether the query can use an existing index with a small change to its shape, which is frequently the case and costs nothing ongoing.

There is a further index consideration that is easy to miss, which is that adding one can change the plan for queries that were previously fine. The optimiser now has a new option and will sometimes choose it wrongly, so an index added to fix one query occasionally degrades another, and the effect appears as an unexplained regression elsewhere.