When Indexes Help and When They Hurt

Concept

Every index is a bet: read speed paid for with write speed, disk and memory. The bet is usually good, and knowing when it is not is what separates useful indexing from index sprawl.

When an index helps

When an index does not help

-- 1. The filter matches most of the table. A scan is genuinely cheaper.
SELECT * FROM employees WHERE status = 'active';   -- 7 of 8 rows

-- 2. Low cardinality: two distinct values across millions of rows
CREATE INDEX idx_is_deleted ON orders (is_deleted);   -- rarely worth it alone

-- 3. The column is wrapped in a function
SELECT * FROM orders WHERE YEAR(order_date) = 2024;   -- index unusable
SELECT * FROM orders WHERE order_date >= '2024-01-01'
                       AND order_date <  '2025-01-01'; -- index usable

-- 4. Leading wildcard
SELECT * FROM customers WHERE name LIKE '%Ltd';        -- unusable
SELECT * FROM customers WHERE name LIKE 'Sun%';        -- usable

-- 5. Type mismatch forces a conversion
SELECT * FROM employees WHERE id = '2';                -- may skip the index

-- 6. A tiny table. Reading three pages beats descending a tree.

Case 1 deserves a note: the optimiser choosing a full scan is often correct. Reading the whole table sequentially can beat millions of random index lookups plus bookmark lookups. The usual crossover is somewhere around 10 to 25 percent of the table, and the optimiser estimates it from statistics.

When an index actively hurts

• Write heavy tables. A logging table taking 10,000 inserts a second pays for every index on every row.
• Bulk loads. Loading a million rows into an eight index table is dramatically slower than loading first and indexing after.
• Random UUID primary keys in InnoDB. Inserts land in random places in the clustered index, splitting pages and fragmenting it.
• Duplicate indexes. Pure cost, zero benefit.
• Wide indexes on wide columns. An index on a VARCHAR(255) is large, slow to maintain and slow to read.

Finding indexes you do not need

-- MySQL 8: indexes that have never been used since the last restart
SELECT * FROM sys.schema_unused_indexes;

-- MySQL 8: indexes that duplicate another
SELECT * FROM sys.schema_redundant_indexes;

-- Index sizes, largest first
SELECT table_name, index_name,
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM   mysql.innodb_index_stats
WHERE  stat_name = 'size' AND database_name = DATABASE()
ORDER BY size_mb DESC;

-- PostgreSQL: usage counts per index
SELECT relname, indexrelname, idx_scan
FROM   pg_stat_user_indexes ORDER BY idx_scan;

Maintenance

-- Refresh the statistics the optimiser plans with
ANALYZE TABLE orders;                    -- MySQL, MariaDB
ANALYZE orders;                          -- PostgreSQL
UPDATE STATISTICS orders;                -- SQL Server

-- Rebuild a fragmented table and its indexes
OPTIMIZE TABLE orders;                   -- MySQL
REINDEX TABLE orders;                    -- PostgreSQL

Stale statistics are a common cause of a query that "suddenly got slow" - the optimiser is choosing from a wrong picture of the data. ANALYZE is cheap and is the first thing to try.

Important rules

• The optimiser chooses; you can only make a good choice available.
• An index is only used if the query's predicate can be expressed as a range over its leading columns.
• Low cardinality columns are still useful as the second column of a composite index.
• Every index slows every write to that table.
• Statistics decide plans; refresh them after large data changes.

Common mistakes

• Adding an index for every slow query without checking whether an existing one could be extended.
• Leaving redundant prefixes such as (a) alongside (a, b).
• Never reviewing indexes again after creating them.
• Blaming the optimiser for a full scan that is actually the cheaper plan.
• Indexing a boolean column on its own and expecting an improvement.

Best practices

• Start from the slow query log, not from the table definition.
• Prefer extending an existing composite index to creating a new one.
• Review unused and redundant indexes on a schedule.
• Drop indexes before a bulk load and recreate them after.
• Run ANALYZE TABLE after any large change, and before concluding a plan is wrong.

Practice

1. Name three queries in the sample schema that would benefit from an index, and one that would not.
2. Why might the optimiser ignore an index on status when 90 percent of rows are 'active'?
3. List the redundant indexes in a table that has (a), (a, b) and (b, a).