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 directly contribute to generating revenue? Of course, they do, and it is because these expenses contribute to the success of companies in significant ways.

In a typical application, we’ll run hundreds of queries against a table to update it for everyone. Every time we run a query that uses an index, we win back the overhead that went into maintaining that index.

An index can also help an UPDATE or DELETE statement by finding the desired rows quickly. For example, the index on the bug_id primary key helps the following statement: