The Indexes Nobody Reads

Indexes accumulate. Each one was added for a reason, usually a good one, usually by someone reasoning correctly about a query that mattered at the time. Nothing ever removes them, because removing an index requires knowing it is unused, and almost nobody checks.

MySQL will tell you. On our production database, over 23.3 hours and 768,115 queries, it counted every read of every index:

604 indexes
414 not read once

That number is too alarming to publish as it stands, and most of the work of this guide is taking it apart.

Two thirds of them cannot be removed anyway

An index can be more than a lookup structure. Some exist to enforce a constraint, a job they do on every write, whether or not a SELECT ever touches them:

kind         total   unread
PRIMARY        154      119
UNIQUE          94       51
secondary      356      244

A PRIMARY key with no recorded reads is not idle — it is how rows are stored, and the number simply reflects that queries reached those rows another way. A UNIQUE index with no reads is still refusing duplicates on every insert. Deleting either because a counter says zero would be a category error.

What remains is 356 secondary indexes, of which 244 were never read — 68.5%. Those are the ones that exist purely to make reads faster and, during this window, made nothing faster.

The part that surprised us

The obvious next step is to work out how much disk they are wasting. They are wasting surprisingly little of it.

all secondary indexes      319.7 MB
unread secondary indexes    51.2 MB   (16.0%)

Two thirds of the indexes by count are one sixth of the storage. The distribution is doing something sensible — the big indexes are the ones on the big tables, and those are the ones being used. What is unread is mostly small: narrow indexes on narrow tables, each costing a few hundred kilobytes.

So if you were hoping for a headline about reclaiming disk, there is not one. Worth knowing, because "unused indexes are wasting space" is the usual framing and it is not what the measurement says.

What an unused index actually costs

The bill arrives in three other places.

The most direct is write amplification. An index is a second structure holding the same data in a different order, and every insert, update and delete has to maintain it. A table with eight secondary indexes does nine writes per row, not one. That cost lands on exactly the tables you write to most.

Schema changes pay it too. Adding a column, changing a type, or copying the table for a migration carries the indexes along. On a large table this is the difference between a fast operation and a maintenance window.

And there is a cost in comprehension. Eight indexes on a table reads as eight claims about how it is queried, and when four of them are false the next person cannot tell which four. They will add a ninth rather than remove a fourth, because adding is safe and removing requires the evidence this guide is about producing.

To put a scale on the first of those: the indexes on this schema occupy 328.1 MB against 268.5 MB of data. There is more index here than data, which is normal and not in itself a fault. It is where the write cost goes.

The window is the whole caveat

These counters are cumulative since the server started and reset when it restarts. Ours had been up 23.3 hours, because it restarted for a deploy the day before.

So "unread" means "unread in 23 hours", and an index that serves a monthly report is indistinguishable here from one that serves nothing. That is a significant gap. Reporting, billing, retention sweeps and year-end jobs are the processes that run rarely and scan widely, and their indexes will look dead in any short window.

There is no clever version of the fix. Read the counters, wait through a full business cycle without a restart, and read them again. An index unread across a month that included a month-end is a different claim from this one, and it is the claim you need before deleting anything.

Running it on your own

SELECT object_name, index_name, count_read
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = DATABASE()
  AND index_name IS NOT NULL
  AND index_name <> 'PRIMARY'
  AND count_read = 0;

Join it to information_schema.statistics to drop the UNIQUE ones, and to global_status for the uptime, without which the result has no meaning. Then leave it alone for a month.

And when you do act, the safe order is: make the index invisible first — ALTER TABLE … ALTER INDEX … INVISIBLE — which leaves it maintained but unusable by the optimiser. If nothing breaks, drop it. If something does, one statement puts it back, which is not true of a drop on a large table.

How this was measured, and what it does not establish

One production MySQL 8.0.46 schema, 604 indexes, over a 23.3-hour window carrying 768,115 queries, read from performance_schema and joined to information_schema and mysql.innodb_index_stats for kind and size. Nothing was changed, and no index was dropped.

The short window is the main caveat. These numbers describe one day of one application's traffic, and a different day would produce a different count. A longer window can only move the number of unread indexes downward, as rarely-used ones finally fire.

These are counts of row-level I/O, not queries. A single query scanning a table can produce thousands of them. The counters are useful for answering "did anything at all touch this", and misleading as a measure of query volume.

An unread index is only a candidate for removal. The measurement identifies them and settles nothing. Deciding is a separate job that needs the query log, the release calendar and someone who remembers why the index was added — and the honest position after one day is that we have 244 questions and no answers.

The counterpart to an index nothing reads is a read that uses no index. That turned out to be most of them.