What does Table does not support optimize doing recreate analyze instead mean

Question

I am working on MySQL 5 5 and trying to do index rebuild using an OPTIMIZE TABLE query  I am getting the error below      Table does not support optimize  doing recreate   analyze instead   What does this mean  Is MySQL engine not allowing Index Rebuild  What is being done behind this message  at MySQL 5 5 Engine level

User · Answer

OPTIMIZE TABLE works fine with InnoDB engine according to the official support article   http   dev mysql com doc refman 5 5 en optimize-table html  You ll notice that optimize InnoDB tables will rebuild table structure and update index statistics  something like ALTER TABLE    Keep in mind that this message could be an informational mention only and the very important information is the status of your query   just OK    mysql gt  OPTIMIZE TABLE foo   ---------- ---------- ---------- -------------------------------------------------------------------    Table      Op         Msg type   Msg text                                                             ---------- ---------- ---------- -------------------------------------------------------------------    test foo   optimize   note       Table does not support optimize  doing recreate   analyze instead     test foo   optimize   status     OK                                                                   ---------- ---------- ---------- -------------------------------------------------------------------

User · Answer

The better option is create a new table copy the rows to the destination table  drop the actual table and rename the newly created table   This method is good for small tables

User · Answer

That s really an informational message   Likely  you re doing OPTIMIZE on an InnoDB table  table using the InnoDB storage engine  rather than the MyISAM storage engine    InnoDB doesn t support the OPTIMIZE the way MyISAM does  It does something different  It creates an empty table  and copies all of the rows from the existing table into it  and essentially deletes the old table and renames the new table  and then runs an ANALYZE to gather statistics  That s the closest that InnoDB can get to doing an OPTIMIZE   The message you are getting is basically MySQL server repeating what the InnoDB storage engine told MySQL server   Table does not support optimize is the InnoDB storage engine saying      I  the InnoDB storage engine  don t do an OPTIMIZE operation like my friend  the MyISAM storage engine  does     doing recreate   analyze instead  is the InnoDB storage engine saying      I have decided to perform a different set of operations which will achieve an equivalent result

User · Answer

Best option is create new table with same properties  CREATE TABLE  lt NEW NAME TABLE gt  LIKE  lt TABLE CRASHED gt   INSERT INTO  lt NEW NAME TABLE gt  SELECT   FROM  lt TABLE CRASHED gt     Rename NEW NAME TABLE and TABLE CRASH  RENAME TABLE  lt TABLE CRASHED gt  TO  lt TABLE CRASHED BACKUP gt   RENAME TABLE  lt NEW NAME TABLE gt  TO  lt TABLE CRASHED gt     After work well  delete   DROP TABLE  lt TABLE CRASHED BACKUP gt

[mysql] What does "Table does not support optimize, doing recreate + analyze instead" mean?

Examples related to mysql