Understanding SQL Indexes and Query Performance

When Not to Index

• Low-cardinality columns queried alone (a boolean, a three-value status) rarely benefit — as covered in the selectivity chapter, the engine often can't eliminate enough rows to make the seek-plus-lookup cheaper than a scan.
• Small tables — if a table comfortably fits in memory and a full scan is already fast in absolute terms, an index adds write overhead for a read benefit nobody will notice.
• Columns that are written far more often than they're queried — the write cost is being paid constantly while the read benefit is rarely collected.
• Redundant indexes — an index on (dept) is already made partially redundant by an index on (dept, salary), since the composite index's leftmost column alone can already serve most of what the single-column index was doing. Most engines expose a way to find unused or redundant indexes; it's worth checking periodically on a real production database, since indexes tend to accumulate over a project's life and rarely get removed once added.