MySQL - DBA Toolkit - Index Usage and Missing Indexes

Index Health: What You Have, What You Are Using, What You Are Missing

Bad index coverage is one of the most common causes of MySQL performance problems — and one of the easiest to miss until a query goes sideways under load.

There are two sides to this. The first is tables that are large but have no meaningful indexes. Those tables will full-scan on every query that touches them. The second is indexes that exist but are never used. Those indexes are not free — they slow down every INSERT, UPDATE, and DELETE on the table.

This script gives you both views.

    Loading…

Reading the Results

Large Tables With No Secondary Indexes

Column	What to Check
`table_rows`	Estimated row count — larger tables hurt more without indexes
`total_mb`	Size of the table — context for how costly a full scan is
`secondary_index_count`	Zero means every non-primary-key lookup will full-scan this table

Index Usage

Column	What to Check
`reads`	How many times this index was used to satisfy a read
`writes`	Write overhead charged to this index on every DML operation
`fetches`	Row fetches via this index

The performance_schema.table_io_waits_summary_by_index_usage view resets on restart. Stats accumulate from the last time MySQL started — newer servers have shorter history.

What to Watch For

Signal	What It Means
Large table with `secondary_index_count = 0`	Every ranged query or join on this table is a full scan
Index with `reads = 0` and high `writes`	The index is never used for lookups but paid for on every write — candidate for removal
Many indexes on a high-write table	Write amplification — each row change updates every index
Duplicate indexes covering the same columns	Redundant overhead — MySQL cannot remove these automatically

Gareth Winterman