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


Tags: , , , , , , , ,
  • Pingback: Optimize only fragmented tables in MySQL | Justin.my()

  • dannyo18oDanny

    Thanks for the tip

  • Djib

    On the mysql website, don’t they write it’s done automatically ?

  • Stif

    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?

  • Cabeza

    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

  • Junseok, Bae

    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.

Translate

Buy me a Caffè Latte

Thank you for the Caffè Latte

2013/02/15 saw art
2013/01/25 Parul Patel
2012/11/20 Barbara Nicholas $10
2012/10/08 Amy Stockman $5
2012/07/15 siewho chang $ 50
2012/06/08 Susan Hampton $10
2012/05/11 Chris Grima $10