What the note 'Table does not support optimize, doing recreate + analyze instead' means
Table does not support optimize, doing recreate + analyze insteadShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
Two rows: a note with this text and a second row with status OK.Normal InnoDB behaviour: nothing is brokenFix - 2
Status 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
Application writes stall during the run, or replicas fall behind afterwards.The rebuild locks writes or fills the binary logFix
- 1
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'\G2Fix
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
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix