Skip to main content

MY.L2.OPTIMIZE_TABLE — OPTIMIZE TABLE rebuilds the whole table

  • Category: safety
  • Level: 2
  • Confidence: deterministic
  • Downtime class: derived from the online-DDL matrix, which leaves it undetermined until the table is known, see below
  • Stability: stable
  • Suites: lint
  • 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.

On InnoDB, OPTIMIZE TABLE is a table rebuild, the same work as ALTER TABLE … FORCE. It costs time and disk in proportion to the table, not to the space it gives back. How the rebuild treats the table's writers depends on one fact about the table:

The tableThe rebuildA write during it
has no FULLTEXT indexin placelands at once
has a FULLTEXT indexa copywaits for the whole run

Measured on MySQL 8.4.10, with a write issued from a second session while the statement ran: on a table without a FULLTEXT index the write landed in 20 ms, and on a table with one it waited two seconds until lock_wait_timeout refused it with error 1205, while the rebuild went on.

A replica repeats the rebuild, because the statement is written to the binary log unless it says NO_WRITE_TO_BINLOG or its alias LOCAL.

Flagged​

DB::statement('OPTIMIZE TABLE orders');

Preferred​

// The same rebuild, refused by MySQL where it would block writes:
DB::statement('ALTER TABLE orders FORCE, ALGORITHM=INPLACE, LOCK=NONE');

Measured on MySQL 8.4.10: on a table without a FULLTEXT index this runs in place, and on a table with one the server refuses it with error 1846 instead of copying the table. It is still a rebuild of the whole table, best run outside the deploy. When the aim is fresh index statistics after a large change, ANALYZE TABLE reads them without rebuilding anything.

What the downtime class says​

The online-DDL matrix answers OPTIMIZE TABLE with a condition: in place while the table has no FULLTEXT index, a copy otherwise. A lint run reads the migration and not the table, so the finding carries an undetermined downtime class that names the condition, table_has_fulltext_index, rather than the cheap case.

Set a lock wait timeout regardless​

The rebuild takes an exclusive metadata lock to start and to finish, and waits for it behind every open transaction on the table. MY.L3.MISSING_LOCK_WAIT_TIMEOUT reports a migration that leaves that wait unbounded.

False positives​

A table without a FULLTEXT index. The rule cannot see the table, so it reports the rebuild that leaves the table writable as well. That rebuild still takes time and disk in proportion to the table.

Sources​

The fix material this rule carries​

A finding from this rule carries a payload whose strategy is none: this rule has looked, and there is no safe standard sequence. That is a conclusion rather than an omission — a finding with no payload at all says only that nobody wrote one.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.