Unused indexes require checks across every database replica node
Unused indexes in the open-source relational database used by many web applications cannot be judged safely from the primary server alone when replicas handle part of the reading workload. An index, a data structure that helps the database server find rows without examining an entire table, may receive no reads on the primary while remaining essential to reporting, dashboard or batch queries elsewhere. Unnecessary indexes still impose costs: the database server must update them during INSERT and UPDATE operations, they consume buffer pool memory, enlarge backups and can lengthen schema changes.
Since version 5.7, the sys schema has included `sys.schema_unused_indexes`, a view that presents indexes with no recorded reads since the server started. Its evidence comes from Performance Schema, the database server's internal collection of operational measurements. Those counters are local to each server. They do not reveal that a reporting replica uses an index for month-end work, that an Aurora reader serves dashboard requests, or that a load balancer sends particular queries to one member of a Galera or Percona XtraDB Cluster. Removing such an index through replicated DDL, the commands that alter database structures, could turn a fast replica query into a full table scan that examines every row.
Restarts create another blind spot because the counters return to zero. A server patched on Tuesday may not yet have observed a weekly batch process or quarterly report. Uptime therefore matters, but no universal number of days proves an index is redundant. The observation period must include the business events and scheduled jobs that make the workload representative.
Percona developed the open source `check_unused_indexes` Python script to combine evidence across a replication topology, the connected primary, replicas and cluster peers that hold related data. Given an inventory and a designated primary, it connects to every listed server and reads `SHOW REPLICA STATUS`, or `SHOW SLAVE STATUS` on older versions, along with `@@wsrep_cluster_name`, Aurora's `information_schema.replica_host_status` where available, and the `Uptime` status variable. It then follows replication downstream, including chained and circular replication, and adds Galera or Percona XtraDB Cluster peers. Aurora readers are associated through server IDs or an alias convention.
Inventory entries unrelated to the chosen primary are excluded, allowing one file to describe a larger fleet. Host matching is done as plain text: if `Source_Host` contains `db-primary.internal`, the inventory must use that hostname rather than an IP address. An inventory can contain one `alias=host:port` entry per line, with port 3306 used when omitted. Servers can also be supplied directly with `--primary` and `--replicas`.
After resolving the topology, the script queries `sys.schema_unused_indexes` on every included node. It always ignores `sys`, the database server's own system schema, `performance_schema` and `information_schema`. It also excludes an index when its first column participates in a foreign key, a rule connecting values between tables, because the database server requires supporting indexes for those constraints. Operators can narrow the check with `--include-schema` and `--include-table`, or add exclusions with `--ignore-schema`.
The final step intersects the results, retaining only indexes reported as unread on every checked node. In Percona's example, the primary reported 12 candidates, one replica reported eight and another reported five. Their uptimes were 42.1 days, 42.1 days and 14.0 days. Only two indexes, `app_db.orders.idx_legacy_status` and `app_db.users.idx_created_at`, appeared unused across all three servers. The other 10 primary-side candidates were serving reads somewhere else. The `-qs` or `--quiet-summary` option prints only the generated commands for use in a ticket or change request.
The script is deliberately conservative about missing evidence. Failure to reach the primary during discovery is fatal. Unreachable inventory entries produce named warnings because the script cannot inspect their replication status or know whether they belong to the topology. If the primary disappears during index collection, processing stops immediately. If a resolved replica or cluster peer fails at that stage, the script exits unless `--force` is supplied. Forced results are explicitly limited to the nodes that answered and should not be used merely to bypass a temporary network problem.
Nodes below the minimum uptime generate a warning after the check. The default threshold is seven days, configurable with `-u`; setting it to zero disables the warning. The testing option `--pause-seconds` inserts a delay between discovery and collection, making it possible to stop a replica deliberately and confirm that the program exits with code 1. Percona recommends testing this failure path before relying on the script in production.
Installation requires Python 3.6 or newer and the Python client library for the database server. Credentials are read from `~/.my.cnf` by default, or another database option file selected with `-c`. The same read-only account should exist on every node and needs permission to read `sys.schema_unused_indexes` and `information_schema`, and to run `SHOW REPLICA STATUS`. The script itself changes nothing. Its single-file project is published under the Apache 2.0 license.
The generated commands are proposals, not automated actions. By default, version 8.0 output makes each candidate an invisible index. The optimizer, the database server's query-planning component, stops selecting an invisible index, but the database continues maintaining it during writes. Changing visibility is a metadata operation and can be reversed in seconds, while dropping and later rebuilding a large index can take hours. `--action drop` prints removal commands, while `--action both` prints visibility and removal forms.
A cautious rollout first reviews every candidate, then makes selected indexes invisible on the primary so the change replicates throughout the topology. Operators should observe at least one complete business cycle, including weekly batches and month-end jobs, while watching query metrics and the slow query log. If performance regresses, `ALTER INDEX ... VISIBLE` restores optimizer access. Version 8.0 also allows a single session to set `use_invisible_indexes=on` in `optimizer_switch`, including through `SET_VAR`, to test whether a particular query would choose the hidden index without exposing it to the application.
Important limits remain. Only inventory-listed servers are checked. Performance Schema must be enabled. `sys.schema_unused_indexes` omits primary keys but can report UNIQUE indexes, which may enforce business rules despite receiving no reads, so candidates should be checked with `SHOW CREATE TABLE`. Aurora grouping depends on server IDs or aliases such as `aurora-reader-1`.