Skip to main content

MY.L7.INDEX_REDUNDANT — An index whose work another index already does

  • Category: performance
  • Level: 7
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • 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.

The cost is real and paid on every write​

An index on (customer_id) beside one on (customer_id, placed_at) answers nothing the second cannot. It is still maintained: every insert writes it, every update touching its column writes it, and it occupies its own pages in the buffer pool. The read side gains nothing at all.

Flagged​

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('customer_id');
    $table->timestamp('placed_at');
    $table->index(['customer_id']);
    $table->index(['customer_id', 'placed_at']);
});

Preferred​

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('customer_id');
    $table->timestamp('placed_at');
    $table->index(['customer_id', 'placed_at']);
});

This rule is mostly exclusions, and that is the design​

Its advice is to drop something. The expensive failure is therefore a false positive, not a miss: dropping the wrong index turns a lookup into a full scan on production, and the advice came with this tool's name on it.

So a comparison is only made between indexes the reading already judged comparable, and every exclusion is a named catalog skip rather than a silent omission:

  • A prefix-length key. KEY (email(20)) indexes the first 20 bytes, not the column — and information_schema reports the same COLUMN_NAME either way. This one was found by the negative corpus rather than reasoned out in advance: the rule reported a prefix key as redundant to a full-column key on a real 8.4 catalog, which is exactly the false positive the corpus exists to catch. It has no PostgreSQL counterpart.
  • A functional index. It has no column name at all in STATISTICS, only an EXPRESSION.
  • FULLTEXT and SPATIAL. They answer a different kind of question entirely; the redundancy question is only meaningful inside one method.

Three more things that stop a pair from being reported​

A unique index or the primary key is never the victim. Dropping either changes what the schema allows, not what it costs. It may still be the index that covers another: a unique key on (a, b) serves lookups on (a).

It has to be a strict left prefix. An index on (a, b) serves (a) and does not serve (b). Order is the fact, not a detail.

Two identical indexes produce one finding, not two. Neither is the obvious victim, so the tie is broken by name — arbitrary, but arbitrary deterministically, so the same schema produces the same report every run. Both names appear in the message and you pick.

Removing it — check the foreign keys first​

DROP INDEX orders_customer_idx ON orders;

DROP INDEX is an in-place operation on InnoDB and copies no table, so there is no CONCURRENTLY to ask for. But an index that carries a foreign key cannot be dropped while the constraint stands: InnoDB requires an index on the referencing columns and refuses the statement rather than leaving the key unindexed. That refusal is a good thing — it is the engine catching what this rule cannot see — but it means the drop may not simply succeed.

SQLens never runs it. The package writes nothing to a database; the statement is here so a human can run it in a window they chose.

Sources​

  • MySQL 8.4: multiple-column indexes — a multiple-column index serves queries testing a leftmost prefix of its columns, which is what makes a separate index over that prefix redundant for reads.
  • MySQL 8.4: foreign key constraints — InnoDB requires an index on the referencing columns and refuses to drop the last one satisfying it, which is why the remediation says to check rather than promising the statement will succeed.