Last updated on December 1st, 2010 at 12:30 am
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
Pingback: Optimize only fragmented tables in MySQL | Justin.my
Thanks for the tip
On the mysql website, don’t they write it’s done automatically ?
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?
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
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.