Review the Index Design

Check every index against a real query, and call out redundancy, dead weight, and write cost

You are reviewing database performance. Given the schema, data volume, and slow queries below, evaluate the indexes one by one: 1) name the query each index serves and quote the WHERE / ORDER BY fragment it covers; 2) flag redundant indexes whose leftmost prefix is already covered, indexes nothing can use, and predicates that defeat them (functions, implicit casts, leading wildcards); 3) estimate the write and storage cost of each addition; 4) end with three columns — keep, drop, add. Do not infer queries from column names.

[schema / indexes / slow queries / row counts]

How to use

Ask 'which SQL statement does this index serve?' If there is no answer, the index is probably just taking up space and slowing writes.

Good for

A few slow queries have sat at the top of the dashboard for weeks, and the indexes added along the way have not helped.
You are about to add an index to a write-heavy table and need to know whether an existing one already covers it.

Not for

The table has a few thousand rows and fits in memory: index tuning will not move the needle.
Write throughput is already the bottleneck: another index makes it worse, not better.

Example

Input

CREATE TABLE orders (... user_id BIGINT, status VARCHAR(16), created_at DATETIME, ...);
INDEX idx_user (user_id), INDEX idx_user_status (user_id,status), INDEX idx_status (status);
SELECT * FROM orders WHERE user_id=? AND status='paid' ORDER BY created_at DESC LIMIT 20;

What you get

`idx_user` is fully covered by the leftmost prefix of `idx_user_status`, so it can go; `idx_user_status` still cannot satisfy the `created_at` sort and will filesort, so evaluate `(user_id, status, created_at)`. `idx_status` depends on how many distinct statuses exist; if there are only a few, it only pays off for bulk scans by status — check that before dropping it. End with keep/drop/add, and state the write and storage cost of each net-new index.

Common mistakes

Reading a non-null `possible_keys` as proof the index was used: if `key` is NULL or rows examined approaches the table size, the index did nothing.
Adding an index for every slow query: on a write-heavy table each redundant index adds another tree to maintain on every insert.

Try tweaking it like this

Add 'estimate disk footprint and write-latency impact per index' and the review moves from 'is it useful' to 'is it worth it'.
Require queries first and indexes second, which prevents the usual index-by-column-name guesswork.