MySQL Optimization – Calling Optimize Table After Alter Table

alter-tableinnodbMySQL

Should I be calling 'optimize table' when changing datatypes of columns, adding indexes or dropping indexes, assuming a table with millions of rows, heavily used, both for reading and writing (InnoDB).

Ignore any possible downtime factor during the optimize-process.

Best Answer

No, you should not do OPTIMIZE TABLE because it is the same as

ALTER TABLE tblname ENGINE=WhateverTheStorageEngineIs;
ANALYZE TABLE tblname;

Thus, doing an ALTER TABLE tblname ... ;of any kind followed byOPTIMIZE TABLE tblname;` would create two temp tables.

BTW when you do OPTIMIZE TABLE tblname; on an InnoDB Table you get this

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                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

So, never use OPTIMIZE TABLE. You could run ANALYZE TABLE tblname; instead.

Changing datatypes of columns ? If and only if there is the possibility of value truncation, you could run ANALYZE TABLE tblname; during off-hours.

Dropping Indexes ? No need to since dropping an index remove all index stats for that index, leaving other indexes alone.

Related Question