CWE-1089, Large Data Table with Excessive Number of Indices, describes a product that uses a large data table with an excessively large number of indices.
MITRE notes that the meaning of large and excessive varies by team. The CISQ standard recommends default thresholds of 1000000 rows for a large table and 3 indices. Unlike many quality entries in this block, MITRE allows this one for mapping real vulnerabilities, with care.
How it happens
An index speeds up reads on the columns it covers. It also has to be updated on every insert, update and delete. Teams add an index whenever a query is slow and rarely remove one. Years later a table with millions of rows has ten indexes, several of which no query uses.
-- Smell: many overlapping indexes on one large table
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);
CREATE INDEX idx_orders_created ON orders (created_at);
CREATE INDEX idx_orders_status ON orders (status);
The first index is covered by the second, so it costs writes and gives almost nothing back.
What an attacker gains
MITRE lists reduced performance, and notes that if the code is reachable by an attacker, the performance problem might introduce a vulnerability. The realistic version is resource exhaustion. If an endpoint lets anyone insert rows into the table, every request pays for all the indexes, so a modest flood of writes can slow the database for everyone.
How to find it
- Query your database catalog for tables above your row threshold and count their indexes.
- Use the database's usage statistics to find indexes that are never read.
- Look for indexes whose columns are a prefix of another index.
- Compare write latency before and after dropping a suspect index on a test copy.
How to fix it
- Drop duplicate and unused indexes after checking usage over a full business cycle.
- Replace overlapping indexes with one composite index that serves the common queries.
- Rate limit endpoints that insert into large tables.
- Review index additions in code review, the same way you review code.
Quick check
Why can too many indexes slow a database down?
Each write must update every index on the table, so more indexes mean more work per insert, update and delete.
