2

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.

  • It may not be necessary to call it every time you alter but you may if you feel that queries to that table are slow or you know that you can re-claim some space after this operation. Hope this help – Nawaz Sohail Jun 24 '15 at 18:44
2

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.

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.