MY.L5.MORPHS_NO_INDEX — Polymorphic column pair with no index leading on its type

• Category: performance
• Level: 5
• Confidence: deterministic
• Downtime class: none — nothing here is a schema change
• 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 id alone names nothing

A polymorphic relation is two columns and one meaning. commentable_id = 7 does not identify a row: there is a post 7, an invoice 7 and a user 7, and only commentable_type says which one is meant. So every access through the relation constrains both columns, always, without exception.

With no index over the pair, each of those reads the whole table. The cost grows with the table rather than with the number of relations, which is why the pattern is comfortable for a year and then is not.

Nothing in the schema hints at it. A foreign key at least announces the relationship it belongs to, and the server can be asked about it. A polymorphic pair is two ordinary columns whose meaning lives entirely in the application.

Unlike a foreign key, no engine writes this index for you

InnoDB creates the index behind a foreign key when the author does not — which is why SQLens has no MySQL twin for PG.L5.FK_NO_INDEX. It does nothing of the kind here. A polymorphic pair is not a declared relationship; it is two ordinary columns whose meaning lives in the application. The server has never been told they belong together, so nobody but the author will ever index them.

Flagged

Schema::create('comments', function (Blueprint $table) {
    $table->id();
    $table->string('commentable_type');
    $table->unsignedBigInteger('commentable_id');
});

Preferred

Schema::create('comments', function (Blueprint $table) {
    $table->id();
    $table->morphs('commentable');
});

morphs() writes both columns and the index. The flagged shape is what hand-written columns produce, and what is left behind when a later migration replaces a nullableMorphs() with its two parts.

The type column has to come first

A B-tree serves a left prefix, so the order decides which questions the index can answer:

Index on	Answers the paired lookup?	Also answers
(commentable_type, commentable_id)	yes	a type-only lookup, which happens
(commentable_id, commentable_type)	yes	an id-only lookup, which does not
(commentable_type)	no — narrower than the pair
(commentable_type, commentable_id, created_at)	yes — the pair is the left prefix

Both of the first two rows serve the relation, and only one of them is the index morphs() writes. The type column leads because it is the low-cardinality half and the half the resolver fixes first; leading with the id spends the index's first level on a column that never appears alone.

That is why an index the wrong way round is reported as its own sentence — replace this index rather than add one. Told merely that no index exists, a reader adds a second one over the same two columns, and MY.L7.INDEX_REDUNDANT then reports that.

Indexes that exist but do not count

Only the indexes the reading judged comparable are counted. On MySQL that excludes a functional index and a prefix index: KEY (commentable_type(20), commentable_id) stores a truncation of the first column, so it cannot answer an equality on the whole value the way the pair needs. A unique key does count — InnoDB implements it with a B-tree over exactly its columns — and so does a wider index whose leading columns are the pair.

An invisible index does not count either, whatever its columns. The server keeps it up to date and it still enforces what it declares, but the optimizer does not use it, so a read through the relation scans exactly as if it were not there: EXPLAIN names neither a key nor a candidate. A pair whose only index is invisible is reported with its own sentence, make that index visible again rather than add one. A second index over the same two columns would only leave the hidden one for MY.L7.INDEX_REDUNDANT to report. If it was hidden on purpose, to try out dropping it, the finding is the answer to that experiment. An invisible index the wrong way round is reported like a visible one: replace it.

False positives, and the one this rule cannot rule out

The pair is identified by naming: a <prefix>_type column holding characters beside a <prefix>_id column holding an integer, or the fixed-width char(36) and char(26) that uuidMorphs() and ulidMorphs() write on this engine. The catalog can prove those columns exist and what they hold. It cannot prove that morphs() wrote them, and nothing in a database can.

So a pair that merely happens to be named this way — a document_type enum beside an unrelated document_id, say — is a false positive by construction. Put it on the ignore list; there is no reading of the catalog that would tell the two apart, and a rule that guessed would be silently wrong in both directions.

The id side deliberately does not accept varchar or text. Laravel writes neither for a morph id, and accepting them would report every pair whose id is a slug.

Why there is no lint half

A migration shows what it writes, not the indexes the table already has or the one a later migration adds. The catalog is where both columns and every index exist at once, so that is where the question is a fact rather than an inference. Column types are not the obstacle: the classifier carries the types each statement defines.

A lint half could decide one shape on its own: a new table whose pair no statement of the run indexes. That is the shape morphs() never produces, because it writes the index with the columns, so it would be a second implementation of this rule's answer for hand-written pairs only. This rule reports those from the catalog wherever sqlens:audit reads the migrated schema, a CI database included.

Sources

• MySQL 8.4: multiple-column indexes — a multiple-column index serves lookups that constrain a leftmost prefix of its columns, and nothing else. That is why the type column has to lead, and why a prefix index over it does not count: it stores a truncation and cannot answer an equality on the whole value.
• MySQL 8.4: CREATE INDEX — the index forms this rule reads, including the unique key InnoDB implements with a B-tree over exactly its columns.
• MySQL 8.4: invisible indexes — an invisible index is not used by the optimizer, and the feature exists to test the effect of removing an index without dropping it. That is why such an index does not count, and why the finding asks for it to be made visible rather than for a second one.
• Laravel: polymorphic relationships — why both columns are always constrained together: the key is unique only within one type, so the relation cannot resolve a row from the id alone.