Antipattern: Using Indexes Without a Plan

When we choose our indexes by guessing, we inevitably make some wrong choices. Misunderstandings about when to use indexes lead to mistakes in one of these three categories:

• Defining no indexes or not enough indexes

• Defining too many indexes, or indexes that don’t help

• Running queries that no index can help

No indexes

We commonly read that a database incurs overhead costs as it keeps an index up-to-date. Each time we use INSERT, UPDATE, or DELETE, the database has to update the index data structures for that table to be consistent so that our subsequent searches are able to use these indexes to find the right set of rows reliably.

We’re trained to think that overheads mean waste. Thus, when we read that databases incur overheads to keep their indexes updated, we instinctively want to eliminate those overheads. Some developers conclude that the remedy is to eliminate the indexes themselves. This advice is common, but it ignores the fact that indexes have benefits that justify the overhead cost they cause.

Not all overhead is waste. Don’t our companies employ administrative staff, legal professionals, and accountants? Don’t they pay for facilities even though these expenses don’t ...