12

As my first message here, i don't know if i have to answer or post a new message. I have the same problem as here and the one and only given answer doesn't help me (except if the documentation is incorrect). "You can make OPTIMIZE TABLE work on other storage engines", but my engine is InnoDB. I thank you for your help. S.

Community
  • 1
  • 1
syndrael
  • 158
  • 1
  • 3
  • 9
  • possible duplicate of [Optimize innodb table](http://stackoverflow.com/questions/2816044/optimize-innodb-table) – Bobby Oct 04 '10 at 13:11
  • 2
    Don't get me wrong, but there's everything clearly explained at the other question. InnoDB does not directly support `OPTIMIZE`, has to do a `ALTER TABLE` instead to rebuilt the indexes. So, yes, it works, it just doesn't tell you that. – Bobby Oct 04 '10 at 13:13

3 Answers3

30

OPTIMIZE TABLE works fine on InnoDB tables. The message that says "Table does not support optimize, doing recreate + analyze instead" is purely informational. You can safely ignore that message.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • 2
    Yes. Documentation now clearly states its is an expected message: http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html – Yves Martin Jun 06 '13 at 07:37
2

You cannot IGNORE the result... that means its doing a VERY intensive operation instead of a light one... one a very large table (>100G) this could be hours instead of minutes.

Joe
  • 31
  • 1
-3

use this

ALTER TABLE table ENGINE='InnoDB';
hhs
  • 716
  • 1
  • 6
  • 22