(Some of this is off-topic.)
- The handling of ADD/DROP INDEX is different for MyISAM and InnoDB.
- Oracle is focusing only on InnoDB.
- InnoDB can allow writes while an index is being built -- by keeping track of what needs to be added to the index. It already has a "Change Buffer" to optimize such. MyISAM would have to invent a similar mechanism, and has no 'transaction' hooks to help.
Therefore, I conclude that all(?) benefits from
ALTER TABLE ... INLINE/INPLACE are handled inside the InnoDB engine, leaving MyISAM out in the cold.
Long ago, one flavor of online
ALTER TABLE, namely adding a item to an
ENUM (with restrictions), was implemented. But that only required modifying the
In one situation in a hundred,
OPTIMIZE TABLE is useful in MyISAM. The need for
OPTIMIZE in InnoDB is much less frequent.
One example (applies to MyISAM only): The table has a lot of churn -- either from
UPDATEs that change the lengths of rows or from
INSERT gives preference to filling in holes in the
.MYD, which can lead to individual rows scattered across multiple disk blocks. This slows down
SELECT, even for 'point queries'.
Are you are deleting "old" rows and adding "new" rows? If so,
- Your MyISAM may need
OPTIMIZE, as explained above. But, with InnoDB,
OPTIMIZE would be mostly a waste.
DELETEing 40M rows locks a MyISAM table for a long time.
- There are other techniques to deal with big deletes.
Partitioning for time series
PARTITIONing, even with MyISAM (before MySQL 8.0), is possible. It might let you turn the time consuming
DELETE into an instantaneous
DROP PARTITION. More
PARTITIONing would probably obviate the need for
OPTIMIZE, even for MyISAM.
- Caveat: Usually when adding partitioning, all the indexes need rethinking.