Bug #108101 Table does not support optimize is misleading
Submitted: 9 Aug 19:41 Modified: 10 Aug 12:21
Reporter: Bill Karwin (Candidate Quality Contributor) (OCA) Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb

[9 Aug 19:41] Bill Karwin
Using OPTIMIZE TABLE on any InnoDB table returns a note that the table does not support optimize, yet it also returns a status of OK. This seems to contradict itself.

InnoDB has been the default storage engine since 2010. MyISAM tables are used as the exception rather than the preferred choice for storage engine. 

OPTIMIZE TABLE actually does work on InnoDB tables and does something useful, even if it isn't exactly the same type of optimization as it was originally designed to do for MyISAM tables.

Reporting a note to users that the table does not support optimize, while in fact executing a change to the table that is something like optimize, is a source of confusion for users. At least it serves no purpose to return this note.

How to repeat:
mysql> select version();
| version() |
| 8.0.29    |
1 row in set (0.00 sec)

mysql> optimize table Accounts; -- or any other InnoDB table
| Table         | Op       | Msg_type | Msg_text                                                          |
| test.accounts | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.accounts | optimize | status   | OK                                                                |

Suggested fix:
Continue to report a status of OK when OPTIMIZE TABLE is used on a InnoDB table, but just remove the note.
[10 Aug 12:21] MySQL Verification Team
Hi Mr. Karwin,

Thank you for your bug report.

However, this is not a bug.

The error message is there, because users expect that OPTIMIZE does many things, including re-arranging pages and index trees, which it does not. It actually does less then ANALYZE statement.

This error message is there also because a table takes a metadata lock during operation and thus it can cause the slowdown in the operations.

Not a bug.