Querious Docs
Account

Server Index Usage view — MySQL

Every index takes time and disk space to maintain, so any index that is unused is purely wasted energy.

The Index Usage view reports how often each index has been used to read rows since the server started, which identifies unused indexes. It also separately lists tables read by a "full scan" (every single row in the full table is individually read and searched), which is where an index is missing rather than wasted.

The Index Usage view
The Index Usage view

The Server Index Usage view looks at indexes server-wide, but is also available in the Structure view of a single schema to view only that database's indexes.

Requirements #

Querious checks that:

  • performance_schema is ON.
  • The global_instrumentation and thread_instrumentation consumers are enabled.
  • The wait/io/table/% instruments are enabled.

If only the consumers or instruments are off, the view shows Index Tracking Is Not Enabled with the exact UPDATE statements to turn them on, and a button to copy them.

The Summary Bar #

Metric What it counts
Unused Indexes — In Active Tables The number of indexes with zero reads on tables that have been read since restart. These are confirmed waste.
Unused Indexes — In Inactive Tables The number of indexes with zero reads on tables nothing has read at all. They can't be judged yet.
Tables w/ Full Scans — Without an Index The number of tables read without using any index. On a large table, that's the expensive kind of query.

Index Usage Mode #

This is the default mode, and it shows one row per index.

Column What it shows
Status Used: read at least once. Unused: zero reads on a table that has been read. Cold: zero reads on a table nothing has read. Full Scans / No Scans: a table-level row rather than a named index.
Schema Server-wide mode only.
Table, Index What the index is and where. A table-level row shows (table scan) as its index.
Reads The number of rows read through this index since restart.
Writes The number of INSERT, UPDATE, and DELETE operations counted against this index. Writes are what make an unused index expensive, since the index has to be maintained.

The Unused Only checkbox shows only indexes with zero reads. The Active Tables Only checkbox (on by default) hides Cold rows so you see only confirmed waste.

Tables with Full Scans Mode #

The segmented control in the view switches to tables read without any index, with the Full Scans count for each. It's the mirror image of the first mode: not indexes costing without helping, but queries with no index to help them.

Recipes #

To find indexes you could probably delete

  1. Check how long the server has been running.

    Running Since in Server Status shows the last restart. A server restarted this morning hasn't seen the monthly reporting job.

  2. Turn on Unused Only and leave Active Tables Only on.
  3. Sort by Writes descending. That's where an unused index costs the most.

  4. Check that each candidate isn't enforcing a constraint before dropping it.

    An index backing a UNIQUE constraint or foreign key enforces correctness rather than being read. See Indexes and Keys.

  5. DROP INDEX the survivors one at a time in the Query view, and watch the application afterward.

To find a missing index

  1. Switch to Tables with Full Scans and sort by Full Scans descending.

    Weigh table size: a thousand scans of a twenty-row table cost nothing, while a hundred scans of a ten-million-row table are the whole problem.

  2. Take the table name to Query Digest to find which statements read it.

  3. Check how those statements read the table.

    Look at their WHERE and JOIN clauses, and their Examined against Rows Sent. A large ratio is a scan finding a few rows the hard way.

  4. Add an index covering those columns, then confirm the scan count stops rising.

To confirm a Cold index is really unused

  1. Turn off Active Tables Only to see Cold indexes.
  2. Leave the server running through your rarest job's cycle: a day, a week, or a month.
  3. Check again. An index still without reads after its table has been read is now Unused, and a real candidate.

Notes #

  • Counts are since server start, or since the performance_schema tables were truncated.
  • Use the Refresh button in the bottom bar to update the data shown.