When Indexes Hurt

When an Index Costs More Than It Saves

Each secondary index is one more B+tree for every INSERT, DELETE and relevant UPDATE to change, plus disk and buffer-pool memory, invisible or not:

The same half-million rows loaded with one index and with fourSQL

CREATE TABLE ins_lean (PRIMARY KEY (id)) SELECT * FROM big_orders WHERE id <= 500000;
CREATE TABLE ins_heavy LIKE big_orders;
INSERT INTO ins_heavy SELECT * FROM big_orders WHERE id <= 500000;

Output

Query OK, 500000 rows affected (3.086 sec)
Records: 500000  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.202 sec)
Query OK, 500000 rows affected (8.733 sec)
Records: 500000  Duplicates: 0  Warnings: 0

Three secondary indexes nearly tripled the load time, and two already outweighed the table in Index Statistics. Prune with evidence:

sys.schema_redundant_indexes flags an index like idx_customer once another starts with its column; exact duplicates draw warning 1831.
sys.schema_unused_indexes counters restart with the server and each ALTER TABLE.
No index can seek on YEAR(ordered_at) = 2026 or LIKE '%.com'; rewrite the predicate (Sargable Predicates) or index the expression (Generated Columns).

MENU

When Indexes Hurt