An index added for a query that was deleted a month later

An index created for a reporting query, and the report replaced by a projection four weeks afterwards.

-- the audit query, run quarterly
SELECT s.table_name, s.index_name,
       COALESCE(u.rows_selected, 0) AS reads
FROM information_schema.statistics s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage u
  ON u.object_name = s.table_name AND u.index_name = s.index_name
WHERE s.table_schema = DATABASE()
GROUP BY 1, 2, 3
HAVING reads = 0;

An index nobody reads costs write throughput and buffer pool on every insert, forever, and nothing surfaces it — a query plan can only tell you about indexes that are used. The usage counters reset on restart, so the audit is only meaningful after a long uptime, which is the caveat that makes people distrust the result.