Skip to main content

MY.L7.INDEX_UNUSED — An index nobody has read, on an engine that cannot say for how long

  • Category: performance
  • Level: 7
  • Confidence: deterministic
  • Downtime class: none
  • Stability: experimental — this rule does not run unless a project opts in
  • Suites: audit
  • Applies to: MySQL 8.4
  • Not transferable to MariaDB: this page describes MySQL 8.4 behavior. MariaDB answers the same driver and does not share these semantics, so SQLens refuses it outright rather than reasoning about it — see drivers/unsupported.

Opt in first​

'stability' => ['experimental'],

MySQL is harder here than PostgreSQL, and the rule says so​

Two gaps, and the second one is permanent.

performance_schema can be switched off. It is a server setting, and on an instance where it is off there is nothing to read at all. An empty answer is therefore genuinely ambiguous in a way PostgreSQL's is not — so the rule reports undetermined rather than treating silence as "no index was used".

There is no reset point. MySQL's counters run from server start, and the engine records no equivalent of PostgreSQL's pg_stat_database.stats_reset. The window's length is not merely unknown today; it is unknowable from the catalog.

The honest consequence, stated plainly: with the shipped 30-day minimum this rule can never conclude on MySQL. It answers undetermined with the reason named. Assuming the counters had been running long enough would be a DROP recommendation resting on nothing at all, which is the one thing worse than no answer.

Getting a verdict anyway, deliberately​

'audit' => [
    'unused_index' => [
        'min_observation_days' => 0,
    ],
],

Zero says report on whatever window the server has. On this engine that is a real decision, not a formality: you are stating that you know when this server last started and accept the counters as they stand. The rule then reports zero-scan indexes and names the fact that the window has no recorded beginning.

Flagged​

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->string('legacy_ref')->nullable();
    $table->index(['legacy_ref']);
});

Preferred​

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->string('legacy_ref')->nullable();
});

Why it is experimental, and it is not about maturity​

Every other rule in this package answers from the schema, and a schema does not move while you look at it. This one answers from counters, so its verdict depends on when it is asked — a direct tension with the promise that the same state produces the same result. The tier keeps that out of everybody else's run.

Two things the counter cannot see​

Statistics are per instance. An index read only on a replica looks unused on the primary.

A query that runs monthly has not run yet if the window is shorter than a month.

Never reported​

A primary key or a unique index. "Nobody queried it" is not an argument about a constraint: that index exists to refuse a write, and it has been doing so silently the whole time.

Removing it — check the foreign keys first​

DROP INDEX orders_legacy_ref_index ON orders;

DROP INDEX is in place on InnoDB, but an index that carries a foreign key cannot be dropped while the constraint stands — InnoDB refuses rather than leaving the key unindexed. SQLens never runs it.

Sources​

  • MySQL 8.4: table_io_waits_summary_by_index_usage — COUNT_STAR per index is the engine's index usage counter. The instrument belongs to performance_schema, which is a server setting and may be off, and MySQL records no reset point for it.