MySQL: How to Audit Unused Indexes in Replica Topologies

The sys.schema_unused_indexes view only sees the server it's queried on. A Percona tool cross-checks results across the primary and replicas before suggesting any removal DDL.

The sys.schema_unused_indexes view only sees the server it's queried on. A Percona tool cross-checks results across the primary and replicas before suggesting any removal DDL.

The Problem the Sys Schema Hides

Every MySQL schema with a few years on the road accumulates an index nobody remembers creating. It came from a retired report, an ORM migration, a 'just in case' that never came in handy. Each of these indexes has a cost: extra writes on every INSERT and UPDATE, more pressure on the buffer pool, larger backups, slower DDL.

Since MySQL 5.7, the sys schema has included the sys.schema_unused_indexes view, built on the Performance Schema's per-index I/O counters. One query lists everything with zero reads since the server started up. It's tempting to turn that list directly into DROP INDEX.

The problem is that these counters are local to the server being queried. They know nothing about what the replicas are doing. In most topologies, the primary handles writes and light OLTP, while heavy reads go elsewhere: a reporting replica that runs the monthly close, an Aurora reader that serves the dashboard, a Galera or Percona XtraDB Cluster node that the load balancer favors for a certain type of query.

An index can show zero reads on the primary and still be the only thing keeping a replica's reporting query under one second. Drop it on the primary, the DDL replicates, and the replica falls back to a full table scan. There's a second treacherous detail: the counters reset on every restart. A server that was patched on Tuesday has only seen a few days of traffic, and a weekly job or quarterly report hasn't even run yet.

How check_unused_indexes Solves This

Arunjith Aravindan published check_unused_indexes on the Percona blog on November 1, 2026, a Python script that asks the same question on every server in a replication topology and only reports an index when the answer is 'unused' on all of them. The project runs in three stages, with two safe stopping points.

In stage 1, the script discovers the topology. It connects to every server in the inventory and reads SHOW REPLICA STATUS (or SHOW SLAVE STATUS on older versions), @@wsrep_cluster_name to identify Galera and PXC members, information_schema.replica_host_status for Aurora, and the Uptime variable. From there it walks out from the primary: a replica whose source matches the primary's address joins the list, then that replica's own replicas, and so on, including chained or circular replication and Galera cluster peers.

In stage 2, the script queries sys.schema_unused_indexes on each resolved node. Two filters always apply: system schemas (sys, mysql, performance_schema, information_schema) are excluded, and any index whose first column participates in a foreign key is excluded, because MySQL requires an index on those columns and wouldn't let you drop it even if you wanted to. You can narrow things further with --include-schema and --include-table, or ignore extra schemas with --ignore-schema.

In stage 3, the script computes the intersection: it starts from the primary's list and removes anything that doesn't also appear on every other node's list. What's left is the set of indexes with zero reads everywhere checked, printed as a table followed by the DDL to run on the primary.

Installation and First Run

The script is a single file. It needs Python 3.6 or newer and the pymysql library.

git clone https://github.com/Percona-Lab/check_unused_indexes.git
cd check_unused_indexes
pip3 install --user pymysql

Credentials come from a MySQL options file, ~/.my.cnf by default (or another one, pointed to with -c). The account needs read access to sys.schema_unused_indexes and information_schema, plus permission to run SHOW REPLICA STATUS. The script only reads; it never changes anything on the servers.

For a quick test, you can list servers directly on the command line. For anything bigger, use an inventory file instead, one alias=host:port per line:

# inventory.txt
db-primary=10.0.0.10:3306
db-replica-1=10.0.0.11:3306
db-replica-2=10.0.0.12:3306
db-analytics=10.0.0.13:3306

python3 check_unused_indexes.py --inventory inventory.txt --primary db-primary

One detail worth checking before the first run: topology discovery compares each replica's Source_Host to the inventory addresses as plain strings. If the replicas were configured with CHANGE REPLICATION SOURCE TO SOURCE_HOST='db-primary.internal', the inventory needs to use that same hostname, not the IP. A smaller-than-expected topology almost always comes down to this.

What the Output Reveals in Practice

In the project's own example, with one primary and two replicas (the db-analytics inventory entry replicates from a different primary and is left out), stage 2 finds 12 candidates on the primary, 8 on the first replica, and 5 on the second. The final intersection comes down to 2 indexes: idx_legacy_status on app_db.orders and idx_created_at on app_db.users.

That gap between 12 and 2 is the whole point of the tool. Expect a gap like this on most real systems.

Arunjith Aravindan, author of check_unused_indexes at Percona

Of the 12 candidates seen in isolation on the primary, 10 were carrying read traffic on some replica. Running sys.schema_unused_indexes alone there would have dropped an index that was sustaining a real query on another node. For anyone who just wants the DDL without the table, the -qs (--quiet-summary) flag trims the output down.

What Happens When a Node Doesn't Respond

The intersection is only as good as its inputs. If a replica goes down and the script simply ignores it, the result looks complete without being complete. The current version handles this case by case:

The stage 1 warning deserves extra attention: if a replica is already down when the script starts, it can't read that replica's replication status and has no way of knowing it belongs to that topology. The only option is to list the inventory addresses that didn't respond. It's worth reading that warning before trusting the result. --force exists for a node that's known to be permanently decommissioned, not as a way around a temporary network blip.

From Candidate to Removed Index

The script prints DDL and stops there: it never executes anything on its own. By default, the suggestion is to make the index invisible instead of dropping it:

ALTER TABLE app_db.users ALTER INDEX idx_created_at INVISIBLE;

Invisible indexes have existed since MySQL 8.0. The optimizer stops using the index, but MySQL keeps maintaining it on every write. It's a metadata change, so undoing it is too, which makes rollback a matter of seconds, quite different from a DROP INDEX followed by recreation, which on a large table can take hours. The rollout that makes sense to follow:

1. Read the list of candidates yourself and confirm that each name makes sense.
2. Run the ALTER ... INVISIBLE statements on the primary; they replicate to every node in the topology.
3. Wait out a full business cycle, including the monthly close and any weekly batch job, while watching the slow query log and metrics.
4. If something gets worse, run ALTER INDEX ... VISIBLE on the affected index and return to the starting point.
5. Only once you're confident, generate the actual DROP INDEX with --action drop (or --action both for both forms of DDL).

Before that last step, you can test a specific query without making the index visible to the whole application, by setting use_invisible_indexes=on in optimizer_switch just for the current session, with SET_VAR(optimizer_switch = 'use_invisible_indexes=on').

Limits Worth Noting

No tool can see traffic that hasn't happened yet, and check_unused_indexes is no exception to that rule. A few points the author himself highlights:

• Only servers listed in the inventory are checked; a forgotten replica is invisible to the script, and so is everything it serves.
• sys.schema_unused_indexes excludes primary keys, but not UNIQUE indexes: a unique index can have zero reads and still be enforcing a business rule. It's worth checking each candidate with SHOW CREATE TABLE before dropping it.
• Statistics start at the last restart; the uptime warning helps, but defining what counts as 'enough time' is up to whoever operates the workload.
• The Performance Schema needs to be enabled; if someone turned it off, the view has nothing to report.
• Aurora readers are identified by server ID or by alias convention (naming the writer aurora and the readers aurora-reader-1, aurora-reader-2), and hosts are compared as literal strings, so the inventory needs to use the same name that shows up in Source_Host.
• The tool finds unused indexes, not duplicate indexes; for redundancy between indexes covering the same columns, the way to go is still pt-duplicate-key-checker from Percona Toolkit.

The project is on GitHub, at Percona-Lab/check_unused_indexes, under the Apache 2.0 license, as a single file that's easy to read before pointing it at something that matters. Group Replication and multi-source replicas don't have declared support yet; anyone who runs into that case has an open channel for issues and pull requests.

Source

1. Percona Database Blog: Multi-Node Unused Index Verification, by Arunjith Aravindan, November 1, 2026.