Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

What the note 'Table does not support optimize, doing recreate + analyze instead' means

ErrorTable does not support optimize, doing recreate + analyze instead

Short answer

It is a note, not an error. For InnoDB, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table and updates its statistics, and the next row says status OK. If the file stays the same size, the table is probably not in its own .ibd file.

  • Applies to MySQL 8.0, MySQL 8.4 LTS, InnoDB
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run it once and read both result rows. A note followed by status OK means the rebuild worked.

    OPTIMIZE TABLE orders;
  2. 2Match what you see, then jump to its fix

    1. 1Two rows: a note with this text and a second row with status OK.Normal InnoDB behaviour: nothing is brokenFix
    2. 2Status OK, but the data directory is no bigger or smaller than before.The table is in the shared system tablespace, so no space comes backFix
    3. 3Application writes stall during the run, or replicas fall behind afterwards.The rebuild locks writes or fills the binary logFix

What does the error look like?

  • Table does not support optimize, doing recreate + analyze instead
  • | shop.orders | optimize | note | Table does not support optimize, doing recreate + analyze instead |
  • | shop.orders | optimize | status | OK |

Cause 1 of 3

Normal InnoDB behaviour: nothing is broken

1Check

The manual shows this exact pair of rows as the output for an InnoDB table. InnoDB does not run a separate optimize step; the statement is mapped to ALTER TABLE ... FORCE.

SHOW TABLE STATUS LIKE 'orders'\G

2Fix

No fix is needed. If you want a statement that says what it does, run the rebuild yourself.

ALTER TABLE orders FORCE;

3Verify

The statement finishes and the table still answers queries.

SELECT COUNT(*) FROM orders;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The table is in the shared system tablespace, so no space comes back

1Check

The manual says the space is reclaimed for the operating system when the table has its own .ibd file, which needs innodb_file_per_table to have been on when the table was created.

SHOW VARIABLES LIKE 'innodb_file_per_table';
SELECT TABLE_NAME, DATA_LENGTH, DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'orders';

2Fix

Keep innodb_file_per_table ON for tables you create, so each gets its own .ibd file. A table created while it was OFF has to be recreated (export it and import it again) to get one.

SET GLOBAL innodb_file_per_table = ON;

3Verify

After the next large delete and OPTIMIZE TABLE, the table's .ibd file in the data directory becomes smaller.

OPTIMIZE TABLE orders;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The rebuild locks writes or fills the binary log

1Check

The rebuild uses online DDL, so the exclusive lock is brief, with two exceptions: tables with FULLTEXT indexes use the table copy method, and so does a server with old_alter_table enabled or started with --skip-new. The statement is also written to the binary log by default.

SHOW VARIABLES LIKE 'old_alter_table';
SHOW INDEX FROM orders;

2Fix

Run it in a quiet window for FULLTEXT tables. To keep it out of the binary log and off replicas, add NO_WRITE_TO_BINLOG, which needs the OPTIMIZE_LOCAL_TABLE privilege.

OPTIMIZE NO_WRITE_TO_BINLOG TABLE orders;

3Verify

The result rows appear as usual; the statement is not written to the binary log.

OPTIMIZE NO_WRITE_TO_BINLOG TABLE orders;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Do not schedule OPTIMIZE TABLE nightly; run it after a large delete, not as routine.
  • Leave innodb_file_per_table ON so freed space can go back to the operating system.
  • Treat a note row as information and read the status row for the outcome.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.