guideline for calling optimize table after alter table?
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&gt; 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.