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.
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.
InnoDB does not support optimize, so does an "empty" alter table instead. If you just did an alter changing structure of the table, then optimize is meaningless. It might have some meaning for INPLACE algorithm of alter in newer MySQL versions, but it would be better do the normal alter instead in the first place.
The short answer is: "Never use OPTIMIZE TABLE on InnoDB tables."
There are exceptions, but they are rare and esoteric. You have not listed such a situation.
