CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 3 of 5 · 1 min read

Over-indexing

Adding more indexes than the actual query workload justifies, paying maintenance cost for little or no read benefit.

Think of a store that keeps a separate catalog for every single attribute a product could have, even attributes no customer has ever searched by. Every one of those catalogs still gets updated for every single sale.

In plain words: over-indexing means a table has more indexes than its actual query patterns justify, often from indexes added speculatively, "just in case," or left behind after a query pattern changed and the index was never removed. Each unused or rarely used index still pays the full index maintenance cost on every write described above, with no corresponding read benefit to show for it, since nothing queries through it. This is a genuinely common mistake in real systems, not a hypothetical one.

A worked example. An index on orders.notes, added speculatively years ago but never actually queried by any report, still pays its full maintenance cost on every insert and update, for zero read benefit.

The key lines. No new SQL; identifying an over-indexed table typically means checking which indexes are actually used, a task covered on the next page's execution-plan reading.

Over-indexing

Over-indexing is having more indexes on a table than its actual query workload justifies, paying full write-maintenance cost on unused or rarely used indexes with no matching read benefit.

Example: a catalog sorted by an attribute no customer ever searches by, still updated on every sale, is an over-indexed structure.