The orders table had forty indexes. Each had been added because a query was slow, each was correct at the time, and none had been revisited — because dropping an index that turns out to matter means rebuilding it on a forty-million-row table under pressure. So they accumulate, and the writes get slower every year.
The symptom
mysql> SELECT COUNT(*) FROM information_schema.statistics
-> WHERE table_schema='shop' AND table_name='orders';
40
mysql> SELECT
-> ROUND(data_length/1024/1024) AS data_mb,
-> ROUND(index_length/1024/1024) AS index_mb
-> FROM information_schema.tables
-> WHERE table_schema='shop' AND table_name='orders';
data_mb index_mb
4,120 11,884 ← the indexes are three times the data
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
Innodb_buffer_pool_reads 4,881,204 # rising steadilyEleven gigabytes of index against four gigabytes of data, on a machine with sixteen gigabytes of buffer pool. The working set does not fit, so every query is reading from disk, and the cause is indexes nobody is using competing for memory with the ones everybody is.
Why it happens
An index is added when a query is slow, which is a good reason. It is never removed because removing it has a risk and no visible benefit — nobody is measured on write throughput, and the cost is spread across every insert rather than concentrated anywhere a person looks.
The specific costs are worth stating because they are invisible individually. Every insert updates every index. Every index occupies buffer pool that could hold data. Every index is read during a table rebuild, which makes schema changes slower. And the optimiser considers each one, which on forty indexes is measurable planning time on a query executed thousands of times a second.
The fix
performance_schema, which has been counting the whole time
SELECT object_name, index_name, count_read, count_write, count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'shop'
AND object_name = 'orders'
AND index_name IS NOT NULL
AND index_name <> 'PRIMARY'
ORDER BY count_read ASC;
-- and the view that wraps it, excluding keys you cannot drop
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'shop';The counters reset on server restart, so a table showing zero reads after two days of uptime proves nothing at all — the query is only meaningful after a full business cycle including the monthly reports and the quarterly export. Checking SHOW GLOBAL STATUS LIKE 'Uptime' first is the step that stops this being a false positive factory.
mysql> SHOW GLOBAL STATUS LIKE 'Uptime';
Uptime 3,847,102 # 44 days. long enough.
mysql> SELECT * FROM sys.schema_unused_indexes WHERE object_schema='shop';
+--------------+-------------+---------------------------+
| object_schema| object_name | index_name |
| shop | orders | idx_legacy_status |
| shop | orders | idx_source_channel |
| shop | orders | idx_customer_ref |
| shop | orders | idx_placed_at_status_alt |
...
17 rows in setThe redundant ones, which are free to find
mysql> SELECT * FROM sys.schema_redundant_indexes
-> WHERE table_schema='shop'G
table_name: orders
redundant_index_name: idx_status
redundant_index_columns: status
dominant_index_name: idx_status_customer
dominant_index_columns: status,customer_id
sql_drop_index: ALTER TABLE `shop`.`orders` DROP INDEX `idx_status`
6 rows in setAn index on (a) is redundant when one on (a, b) exists, because the composite serves every query the single-column one could — and both are being maintained on every write. Six of these were free wins requiring no evidence at all beyond the definition.
The generated drop statement is convenient and worth reading rather than executing blindly: a redundant index can still be the better choice when it is dramatically narrower and the table is write-heavy, since a narrower index means more of it fits in memory. Reversing the column order produces a genuinely different index, so (a, b) and (b, a) are not redundant with each other.
Invisible indexes, which is what makes this safe
-- the optimiser stops considering it. writes still maintain it.
ALTER TABLE orders ALTER INDEX idx_legacy_status INVISIBLE;
-- a week of production traffic later, one of two things:
ALTER TABLE orders DROP INDEX idx_legacy_status; -- nothing broke
ALTER TABLE orders ALTER INDEX idx_legacy_status VISIBLE; -- something did
-- and the visibility audit
SELECT index_name, is_visible FROM information_schema.statistics
WHERE table_schema='shop' AND table_name='orders' AND is_visible='NO';Making it visible again is instant because the index was never dropped and has been kept up to date the whole time — which is the entire point and is what turns a one-way decision into a reversible experiment. The cost is that writes keep paying during the trial, so this is evidence-gathering rather than a saving; the drop still has to follow.
A week is the minimum and a month is better on any system with monthly reporting. The failure mode of a short trial is dropping an index that only the quarter-end export uses, and finding out in January.
Watching the right thing during the trial
# not "is the site slow" — that is too coarse to attribute
# the slow log, digested, before and after
$ pt-query-digest --since '2019-11-04' --until '2019-11-11' slow.log
> week-invisible.txt
$ diff <(awk '/^# Rank/,0' week-before.txt | head -20)
<(awk '/^# Rank/,0' week-invisible.txt | head -20)
# and the specific signal: a plan that changed
mysql> SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED/COUNT_STAR AS avg_rows
-> FROM performance_schema.events_statements_summary_by_digest
-> WHERE SCHEMA_NAME='shop'
-> ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 5;Rows examined per execution is the metric that changes immediately and unambiguously when an index disappears from a plan, and it changes before anybody notices a page being slow. Watching wall-clock latency instead means waiting for the effect to become large enough to see through the noise, which on an under-used index may never happen and on a heavily used one is an incident.
Verifying it worked
-- 23 indexes dropped over six weeks, in four batches
mysql> SELECT COUNT(*) FROM information_schema.statistics
-> WHERE table_schema='shop' AND table_name='orders';
17
mysql> SELECT ROUND(index_length/1024/1024) AS index_mb
-> FROM information_schema.tables WHERE table_name='orders';
4,204 -- was 11,884
-- the number that justified the work
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
Innodb_buffer_pool_read_requests 8,412,004,112
Innodb_buffer_pool_reads 881,204
-- hit rate 99.99%, was 99.42%
-- and the insert, measured
-- INSERT INTO orders: 4.1ms → 1.8msThe buffer pool hit rate moving from 99.42% to 99.99% sounds like a rounding difference and is a factor of fifty in disk reads, which is why the ratio is a poor way to present it. The absolute read count is the better number for anybody outside the team.
Halving the insert time is the result that matters for the write path and is the one nobody was measuring before. It is worth capturing as a benchmark rather than a recollection, because the next person to add an index should be able to see what it costs.
What this costs
A maintenance habit nobody schedules. This exercise took six weeks of elapsed time and perhaps a day of actual work, and it will need repeating in two years — the same pressures that produced forty indexes have not gone away. Putting the schema_unused_indexes query in a quarterly checklist is the obvious answer and it is the kind of checklist item that gets skipped, because the finding is never urgent.
There is also a real risk of dropping something that mattered, and the invisible-index trial reduces it rather than removing it. An index used only by a query that runs at year end will survive a month-long trial and be dropped, and the rebuild will happen in January under pressure. Keeping the CREATE INDEX statements for everything dropped, in a file in the repository with the date and the evidence, is what makes that recovery ten minutes instead of an investigation.