When Not to Index

Indexing is not automatically a win — knowing when to skip it matters just as much

After 14 topics building the case for indexes, it's equally important to recognize situations where adding an index provides little or no benefit, while still incurring its very real costs (storage, and the write overhead from 20.14). Over-indexing is a common, real-world performance mistake.

Case 1: small tables

If a table has only a few hundred rows (unlike our 1,000,000-row students example), a full table scan is already extremely fast — reading a few hundred rows takes microseconds to low milliseconds. An index adds overhead (storage, write cost, and even a small amount of optimizer decision-making overhead) without meaningfully speeding up a scan that was already fast. A lookup table of, say, 50 department records doesn't need an index on department_name.

Case 2: low-selectivity columns

As established in 20.12, a column like is_active (2 distinct values) provides almost no narrowing benefit. Indexing it wastes storage and write overhead for a structure the optimizer will likely ignore in favor of a full scan anyway, since even after using the index, most of the table's rows would still match.

Case 3: columns rarely used in WHERE / JOIN / ORDER BY

An index that's never actually referenced by any query's filter, join condition, or sort clause provides zero read benefit while still costing storage and write overhead on every relevant change. Before adding an index, it's worth confirming the column is actually queried in a way that would use it (e.g., checking real query patterns or execution plans) — indexing "just in case" is a common source of index bloat.

Case 4: write-heavy tables

On a table dominated by high-frequency INSERT/UPDATE/DELETE traffic (e.g., an event log, a high-throughput orders table), the cumulative write-overhead cost from 20.14 can outweigh the read benefit, especially if reads against that table are rare or non-critical-path. Each additional index compounds this cost.

Case 5: over-indexing in general

Perhaps the most important general lesson: each additional index is a real, ongoing cost, not just an upfront one. It's tempting to add an index "to be safe" whenever a new query pattern appears, but a table accumulating dozens of rarely-used indexes over time pays a steep, compounding write-performance tax for benefits that may only apply to a handful of infrequent queries. Periodically auditing and removing genuinely unused indexes (many engines expose usage statistics for this) is a legitimate maintenance practice.

Edge cases

• Sometimes an index that looks "unnecessary" by usage statistics is actually enforcing a constraint (like a unique index backing a UNIQUE/PRIMARY KEY declaration, 20.9) — removing it would remove the constraint too, so usage-based cleanup must be done carefully.
• A column can be low-selectivity overall (20.12/20.13) yet still worth indexing as part of a composite index's leading column, if paired with a more selective trailing column and the combined filter is common — the isolated-column analysis in this topic doesn't automatically apply to composite scenarios.
• What counts as "small" or "write-heavy enough to skip indexing" isn't a fixed universal number — it depends on actual query latency requirements, table growth trajectory, and the specific engine's behavior; these guidelines are heuristics, not hard rules.

Interview-style Q&A

Q: Give two concrete reasons you might deliberately choose NOT to index a column. A: (1) The column has low selectivity (few distinct values, so the index barely narrows the search), and (2) the table is small enough that a full scan is already fast, making the index's overhead pure cost with no real benefit.

Q: Why is "just add an index to be safe" a risky default habit? A: Because every index carries a real, ongoing write-maintenance cost (20.14), so indexes added for rarely-used or low-value query patterns accumulate into a compounding tax on every write, without matching read benefit.

Key takeaway: this whole chapter's message is not "index everything" but "index deliberately" — every index is a considered trade-off, and the right answer is sometimes to skip it.