Beyond COUNT_STAR=0: Auditable MySQL Index Removal with performance_schema

Conclusion First: COUNT_STAR = 0 Is Only a Candidate Signal

The performance_schema.table_io_waits_summary_by_index_usage table can tell you whether any SQL handler I/O events were attributed to a given index within the current statistics window. It cannot prove that an index is safe to drop.

An index should enter the "set to invisible" manual-approval queue only when all of the following hold:

No index-usage increment across a representative cross-instance window
∩ No Performance Schema capacity-loss increment in that window
∩ No structural duties (PK, unique, FK)
∩ No explicit index hints or low-frequency job dependencies
∩ Owner, SLO, rollback DDL, and a full business-cycle canary plan exist

Candidate ≠ delete command. Index size, write rate, and redundancy only prioritize candidates; they never alone justify deletion.

Near-Production Case: Order Table with 11 Secondary Indexes

Anonymized from multiple production incidents. Table app_db.orders runs for three years, ~22M rows/day. During a big-promotion load test, write-transaction P99 jumped from 48 ms to 310 ms; QPS flat, CPU 56%, but redo generation, dirty-page ratio, and storage write latency kept climbing.

Inventory: 1 PK, 2 unique, 11 ordinary secondary indexes. Index idx_legacy_status (legacy_status, created_at) showed COUNT_STAR = 0 on the primary over the last 14 days. Dropping it immediately would still be wrong because:

14-day window misses month-end reconciliation.

Reporting replicas might still use it.

Performance Schema capacity loss (missed events) is unknown.

It could be referenced by FORCE INDEX or archival scripts.

Even if reads don't regress after hiding, true write savings are only measurable after deletion.

Correct practice: establish a 35-day cross-node observation window; after zero usage on all nodes, no stats loss, and clean code scan, set the index invisible, cover one month-end, then drop in low traffic and compare redo bytes/s, write P99, and replica lag under identical throughput.

The point is not "this index must go" but turning a guess into an auditable decision.

Calibrate Your Understanding of Performance Schema

What It Actually Counts

table_io_waits_summary_by_index_usage

aggregates wait/io/table/sql/handler events per table and index:

COUNT_STAR : All handler I/O events attributed to that index access path. Cannot infer: Physical disk I/O count.

COUNT_FETCH / COUNT_READ : Cumulative handler read/fetch operations. Cannot infer: SQL request count.

COUNT_WRITE : Cumulative insert/update/delete handler operations. Cannot infer: Secondary B+tree maintenance count, redo bytes.

SUM_TIMER_WAIT : Sum of timed handler waits. Cannot infer: End-to-end SQL latency or pure disk latency.

INDEX_NAME IS NULL : Event used no index; inserts also land here. Cannot infer: All these events are full table scans.

Official docs state inserts are counted under INDEX_NAME = NULL, and index structure changes may reset per-index stats. Therefore you cannot treat high NULL counts as full scans, nor stitch counts across DDLs.

Three "Cold Index" Categories Must Be Separated

Strictly unused: representative window COUNT_STAR increment = 0
Read-side unused: COUNT_FETCH increment = 0, but other handler events may exist
Low-frequency: tiny COUNT_FETCH, yet may serve month-end, audit, drills, or incident response

Automation should only flag ordinary indexes from the first category as candidates; the other two require human analysis.

Four Mandatory Checks Before Collection Goes Live

1. Version & Instrument

SELECT @@version AS mysql_version,
       @@version_comment AS distribution,
       @@global.performance_schema AS performance_schema_enabled;

SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME = 'wait/io/table/sql/handler';

ENABLED

must be YES. If TIMED = NO, counts work but timer columns cannot judge latency. This guide covers Oracle MySQL 8.0/8.4 only; MariaDB, Percona Server, and cloud-managed variants must be validated separately for fields and DDL capabilities.

2. Don't Just Check "Historical Value Is Zero"

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Performance_schema_index_stat_lost',
  'Performance_schema_table_instances_lost'
);

These are instance-lifetime cumulative counters. Requiring "current value must be 0" would permanently disable the inspector after a single past capacity event; ignoring them treats missed events as non-usage. Reliable approach: persist both values alongside the observation window; if either increases relative to the window baseline, invalidate the window and rebuild the baseline. performance_schema_max_index_stat exceeding capacity increments Performance_schema_index_stat_lost; default is auto-sized.

3. Persist Window Identity

SELECT @@global.server_uuid AS server_uuid,
       VARIABLE_VALUE AS uptime_seconds
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Uptime';

The collector must persist: server_uuid, boot epoch, window start, last counts, index definition hash, and lost-counter baselines. Any of the following resets the window for that table: restart, counter rollback, index definition change, pipeline-reported DDL, lost-counter growth within the window.

4. Let the Window Cover Business, Not Calendar

"Observe 7 days" has no universal validity. Order systems need release day, weekend, promo, refund, and month-end; finance adds quarter-end; DR systems need a switchover drill. sys.schema_unused_indexes is only meaningful when the server has run long enough under representative workload.

Evidence-Chain SQL: Each Query Answers One Question

Index Definition: Don't Just Look at Names

SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME,
       MIN(s.NON_UNIQUE) AS NON_UNIQUE,
       MIN(s.IS_VISIBLE) AS IS_VISIBLE,
       GROUP_CONCAT(
         CONCAT(
           COALESCE(s.COLUMN_NAME, CONCAT('(', s.EXPRESSION, ')')),
           IF(s.SUB_PART IS NULL, '', CONCAT('(', s.SUB_PART, ')')),
           IF(s.COLLATION = 'D', ' DESC', '')
         ) ORDER BY s.SEQ_IN_INDEX SEPARATOR ', '
       ) AS INDEX_COLUMNS,
       MAX(CASE WHEN s.SEQ_IN_INDEX = x.max_seq THEN s.CARDINALITY END) AS LAST_KEYPART_CARDINALITY
FROM information_schema.STATISTICS AS s
JOIN (
  SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_seq
  FROM information_schema.STATISTICS
  GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
) AS x ON x.TABLE_SCHEMA = s.TABLE_SCHEMA
       AND x.TABLE_NAME = s.TABLE_NAME
       AND x.INDEX_NAME = s.INDEX_NAME
JOIN information_schema.TABLES AS t
       ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
      AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.ENGINE = 'InnoDB'
  AND t.TABLE_TYPE = 'BASE TABLE'
  AND s.TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
GROUP BY s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
ORDER BY s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME;

Taking the last key part's CARDINALITY is only a display estimate. Do not use MAX() of composite index column cardinalities as a selectivity conclusion, let alone a deletion criterion.

Strictly Unused: Generate Candidates Only

WITH index_meta AS (
  SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME,
         MIN(NON_UNIQUE) AS NON_UNIQUE, MIN(IS_VISIBLE) AS IS_VISIBLE
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
  GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
)
SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.INDEX_NAME,
       i.NON_UNIQUE, i.IS_VISIBLE,
       COALESCE(p.COUNT_STAR, 0) AS TOTAL_OPS,
       COALESCE(p.COUNT_FETCH, 0) AS FETCHES
FROM index_meta AS i
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage AS p
       ON p.OBJECT_SCHEMA = i.TABLE_SCHEMA
      AND p.OBJECT_NAME = i.TABLE_NAME
      AND p.INDEX_NAME = i.INDEX_NAME
WHERE i.INDEX_NAME <> 'PRIMARY'
  AND COALESCE(p.COUNT_STAR, 0) = 0
ORDER BY i.TABLE_SCHEMA, i.TABLE_NAME, i.INDEX_NAME;

This is an instantaneous snapshot, only for verifying the persistent inspector's output. Quick look via:

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
ORDER BY object_schema, object_name, index_name;

Redundancy, Size & Write Pressure: Only for Prioritization

SELECT table_schema, table_name,
       redundant_index_name, redundant_index_columns,
       dominant_index_name, dominant_index_columns, subpart_exists
FROM sys.schema_redundant_indexes
ORDER BY table_schema, table_name, redundant_index_name;

SELECT OBJECT_SCHEMA, OBJECT_NAME,
       COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql','sys','performance_schema','information_schema')
ORDER BY COUNT_WRITE DESC
LIMIT 50;

SELECT database_name AS TABLE_SCHEMA,
       SUBSTRING_INDEX(table_name, '#P#', 1) AS TABLE_NAME,
       index_name AS INDEX_NAME,
       SUM(stat_value) AS INDEX_PAGES,
       SUM(stat_value) * @@global.innodb_page_size AS APPROX_INDEX_BYTES
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
  AND database_name NOT IN ('mysql','sys','performance_schema','information_schema')
GROUP BY database_name, SUBSTRING_INDEX(table_name, '#P#', 1), index_name
ORDER BY APPROX_INDEX_BYTES DESC;

schema_redundant_indexes

is valuable but still requires checking uniqueness, prefix length, ordering, functional expressions, and covering columns; its sql_drop_index must not enter an automated execution pipeline.

Link Index Changes to SQL Regression

SELECT SCHEMA_NAME, DIGEST, LEFT(DIGEST_TEXT, 240) AS DIGEST_TEXT,
       COUNT_STAR AS EXEC_COUNT,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS TOTAL_SECONDS,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
       SUM_NO_INDEX_USED, SUM_NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 100;

Before changes, save digest snapshots and EXPLAIN FORMAT=JSON for key SQL; after changes, compare per-call latency, rows examined, and no-index-used counts under the same digest and similar traffic window. EXPLAIN ANALYZE actually executes the statement; run only in safe environments or on confirmed read-only SQL.

Hard Gates: These Objects Never Enter Automated Candidates

Explicit PRIMARY : Clustered organization and row identity depend on it; cannot be hidden.

All unique indexes : May enforce business uniqueness; conservative policy avoids auto-handling.

Implicit primary key : When no explicit PK, first UNIQUE NOT NULL may act as PK; also cannot be hidden.

Foreign-key related indexes : Support parent/child constraints; dropping may fail or alter constraint design.

FULLTEXT , SPATIAL , or unsupported index types : Usage patterns, DDL capabilities differ from ordinary B-tree.

Found USE/FORCE/IGNORE INDEX hints : Hiding may cause immediate errors.

Any node shows usage increment : Single-node non-usage ≠ global non-usage.

Window saw restart, DDL, counter rollback, or lost increment : "Zero usage" lacks continuity.

Low-frequency cycle not covered or no owner : Cannot endorse business risk.

Covering indexes cannot be eliminated solely on "same leftmost prefix"; the longer index may avoid lookups or filesort. Implicit primary key is a commonly missed boundary; MySQL explicitly forbids making it invisible.

Runnable Inspector: State Model Over One-Off SQL

Deliverable Structure

Ship the inspector as a standalone project, not just code snippets:

mysql-index-debt-audit/
├─ index_debt_audit.py      # read-only collection; never runs ALTER
├─ requirements.txt          # PyMySQL==1.1.1
├─ tests/
│   ├─ test_window_reset.py
│   └─ fixtures/
├─ Dockerfile
├─ k8s-cronjob.yaml
└─ README.md                # permissions, sample output, runbook

Each MySQL instance gets its own SQLite state DB. Aggregation layer rolls up by "business cluster + schema + table + index definition hash" across all primaries, read replicas, reporting nodes, and promotable replicas; if any node shows a usage increment, the global non-usage conclusion is revoked.

Required State & Window Algorithm

Per (server_uuid, schema, table, index) store:

definition_hash, boot_epoch, window_start,
last_ops, window_ops,
last_fetches, window_fetches,
last_table_writes, window_table_writes,
baseline_index_stat_lost, baseline_table_instances_lost,
reset_count

Collection logic:

First sight of index: establish baseline, window_ops = 0
Same instance, definition unchanged, counters not rolled back, lost not grown: accumulate delta
Restart / definition change / any counter rollback / lost growth: invalidate old window, rebuild baseline
Window reaches minimum duration AND window_ops = 0 AND hard gates pass: emit candidate

The key is the increment of lost. Below is the reset judgment ready for the inspector; it avoids "lost once historically, so tool permanently broken":

def window_must_reset(previous, current, meta, boot_epoch, lost):
    if previous is None:
        return True
    if previous["definition_hash"] != meta.definition_hash:
        return True
    if abs(previous["boot_epoch"] - boot_epoch) > 120:
        return True
    if current["ops"] < previous["last_ops"]:
        return True
    if current["fetches"] < previous["last_fetches"]:
        return True
    if current["table_writes"] < previous["last_table_writes"]:
        return True
    return (
        lost["Performance_schema_index_stat_lost"]
        > previous["baseline_index_stat_lost"]
        or lost["Performance_schema_table_instances_lost"]
        > previous["baseline_table_instances_lost"]
    )

For "cumulative zero both before and after DDL", counters cannot self-prove whether a reset occurred. The release pipeline must invoke a command like --reset-table app_db.orders to purge that table's state and re-observe; this is not optional optimization but a correctness requirement for closure.

Candidate Report Format

{
  "cluster": "orders-prod",
  "server_uuid": "...",
  "schema": "app_db",
  "table": "orders",
  "index": "idx_legacy_status",
  "definition": "(legacy_status, created_at)",
  "observation_days": 35.2,
  "window_ops": 0,
  "window_fetches": 0,
  "window_table_write_ops_per_second": 620.4,
  "approx_index_bytes": 8589934592,
  "reasons": ["no_handler_events", "large_index", "write_hot_table"],
  "remaining_manual_gates": [
    "cross-instance aggregate passed",
    "index-hint scan",
    "month-end workload confirmation",
    "owner and DBA approval"
  ]
}

Scores only prioritize (e.g., redundant +3, >1 GiB +2, write-hot +2); high score must not trigger auto-delete. Inspector's sole output is evidence report and pending-review ticket.

Four Mandatory Automated Tests

First collection : Baseline established, no candidate emitted.

Second collection shows COUNT_STAR increment : window_ops > 0, no candidate.

Restart, DDL, counter rollback, or lost-counter growth : Window resets, re-observation starts.

Ordinary index 35 days zero increment, no structural gates : Emits candidate and ALTER ... INVISIBLE suggestion; no auto-execute action.

Without these tests and sample reports, "runnable" is just code display, not reliable delivery.

From Candidate to Deletion: Two-Phase Verification

Phase A: Invisible Index Validates Read Path

First save SHOW CREATE TABLE, exact rebuild DDL, key digests, plan snapshots, baseline metrics, and owner. Then scan app repos, reporting scripts, stored procedures, and ops scripts:

rg -n --glob '*.{sql,java,kt,go,py,js,ts,xml,yml,yaml}' \
  'USE[[:space:]]+INDEX|FORCE[[:space:]]+INDEX|IGNORE[[:space:]]+INDEX|idx_legacy_status'

After confirmation, execute:

ALTER TABLE app_db.orders
ALTER INDEX idx_legacy_status INVISIBLE;

Invisible indexes are excluded from optimizer choices by default but still maintained on writes and still enforce uniqueness. This phase only validates "read path works without it"; it cannot measure write-cost savings. MySQL positions it as a non-destructive removal-effect test.

If regression occurs, stop-loss action:

ALTER TABLE app_db.orders
ALTER INDEX idx_legacy_status VISIBLE;

To troubleshoot a specific read statement, temporarily let optimizer consider invisible indexes:

EXPLAIN
SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */
       order_id, status
FROM app_db.orders
WHERE legacy_status = 'PENDING';

Canary must cover at least one full business cycle, monitoring: core API success rate and P95/P99, key digest avg latency and rows examined, slow-log growth, tmp-table/filesort, physical reads, plus reporting jobs and replica lag.

Phase B: Post-Delete Write Benefit Verification

First check for long transactions and metadata locks; in low traffic use explicit DDL capability constraints:

SET SESSION lock_wait_timeout = 5;

ALTER TABLE app_db.orders
DROP INDEX idx_legacy_status,
ALGORITHM=INPLACE,
LOCK=NONE;

For InnoDB ordinary secondary indexes, docs list DROP INDEX as in-place, concurrent DML; but it still waits for active transactions on the table and must be rehearsed in target version, cloud implementation, and large-table environment.

After deletion, rollback via "make visible" is impossible; only rebuild:

ALTER TABLE app_db.orders
ADD INDEX idx_legacy_status (legacy_status, created_at),
ALGORITHM=INPLACE,
LOCK=NONE;

Therefore the true fast stop-loss window is the invisible phase, not after deletion.

Proving the Case Actually Gained Benefit

For the anonymized order case, record baseline, invisible phase, and post-delete in a single acceptance table. All three windows should use same-type business days, similar write throughput, and similar cache state.

Core read digest P99 : Baseline → Invisible → Post-Delete. Judgment: Invisible phase must not regress.

Core write API P99 : Baseline → Invisible → Post-Delete. Judgment: Write benefit only seen post-delete.

Rows examined / call : Baseline → Invisible → Post-Delete. Judgment: Guard against plan drift.

redo bytes/s : Baseline → Invisible → Post-Delete. Judgment: Compare under same throughput.

Storage write IOPS / fsync : Baseline → Invisible → Post-Delete. Judgment: Exclude backup and background tasks.

Replica lag P99 : Baseline → Invisible → Post-Delete. Judgment: Watch DDL and write-pressure changes.

Snapshot SQL (all cumulative; compute rates at fixed intervals):

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_os_log_written',
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_pages_dirty',
  'Innodb_data_writes',
  'Innodb_data_fsyncs'
);

Only when write P99 drops, redo bytes/s and storage write pressure fall, and key digests plus reporting jobs show no regression can the improvement be reasonably attributed to index governance; "no alerts" does not constitute proof.

Executable Release Checklist

Observation Integrity

performance_schema

and wait/io/table/sql/handler enabled.

Persisted server_uuid, boot identity, window, counts, and lost-counter baselines.

No restart, DDL, counter rollback, or lost-counter growth in this window.

Covered month-end, promo, batch, switchover, etc., for the relevant business cycle.

Primary, read replicas, reporting nodes, and promotable replicas all collected and aggregated.

Structure & Dependencies

Not explicit or implicit PK, not unique, FK-related, or special index type.

Checked functional expressions, prefixes, ordering, covering queries, and redundancy differences.

Scanned USE INDEX, FORCE INDEX, IGNORE INDEX, and index-name references.

Saved SHOW CREATE TABLE, full definition, and exact rebuild DDL.

Canary & Acceptance

Set invisible and covered full business cycle.

Defined read-side SLO, write-side benefit metrics, owner, and stop-loss deadline.

Rehearsed or confirmed target environment supports expected DDL algorithm and lock level.

Post-delete, completed read-regression and write-benefit acceptance under same workload.

Closing

Mature index governance doesn't ask "is this index zero-usage today?" It asks: does this zero cover real business? Are all nodes consistent? Are statistics complete? Does the index carry structural duties? Who regresses if hidden? Can the deletion benefit be quantified?

When evidence answers all these, indexes cease to be anonymous historical baggage and become observable, canary-testable, auditable engineering assets.