Optimitze Table, MyIsam I was reading a post on Percona (https://www.percona.com/blog/2010/12/09/mysql-optimize-tables-innodb-stop/) that said dropping and adding an index on a innodb table was almost 20* faster than using the table optimize syntax. Is this also true for myisam tables?

I have a 50 million row table and am deleting about 40k rows a day from it (while also adding in 50k new rows).
(Some of this is off-topic.)

Fast ALTER


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 .frm file.

OPTIMIZE

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 DELETE+INSERT.  MyISAM's 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'.

Time Series

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
Using PARTITIONing would probably obviate the need for OPTIMIZE, even for MyISAM.
Caveat:  Usually when adding partitioning, all the indexes need rethinking.