Table does not support optimize, doing recreate + analyze instead

Everytime you do optimize MySQL, by using mysqlcheck -A -o or using ./mysql_optimize from here.
You may see the output

Table does not support optimize, doing recreate + analyze instead.

It is because the table that you are using is InnoDB.

You can optimize the InnoDB tables by using this.


ALTER TABLE table.name ENGINE='InnoDB';




This will create a copy of the original table, and drop the original table, and replace to the original place.
Although this is safe, but I suggest you do backup and test first before doing this.

For more additional information, you should read in here

6 thoughts on “Table does not support optimize, doing recreate + analyze instead”

  1. Pingback: Optimize only fragmented tables in MySQL | Justin.my

  2. hello, I tried this ALTER TABLE table.name ENGINE=’InnoDB’; but the fee data is not changing. The value of free data is still the same as before. How do I determine that there is a improvement?

  3. This is not necessary, in spite of the somewhat misleading message, OPTIMIZE TABLE works for InnoDB. From the 5.5x manual on the MySQL website:

    InnoDB Details

    For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

    mysql> 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 |
    +———-+———-+———-+————————————————————————————–+

    (it is MySQL after all… you expect the message to be slightly misleading :-) )
    Cheers

  4. Excuse me, your explanation is helpful but not necessary. The errorlike message ” Table does not support optimize, doing recreate + analyze instead” is not error but notice if the table is InnoDB. I wrote for future reader of this page because when I search it on Google, this page is first appear. ps) I wrote about 25 books of this field and manage 11 db server of enterprise companies.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.