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).

  • Just wondering why you are still using MyISAM? It's now deprecated! Do you have a compelling reason not to convert your tables to InnoDB?
    – Vérace
    Commented May 21, 2018 at 16:54
  • @Vérace Went through about 10 iterations of creating the same table in innodb and every variation performed worse. Commented May 21, 2018 at 17:04
  • How much worse?
    – Vérace
    Commented May 21, 2018 at 17:07
  • Considerably. Some queries took +50 minutes in the innodb format, vs. 2 minutes in myisam. Percona said This causing heavy consumption for RAM bandwidth and CPU cycles. The table is smaller in myisam format and you can get faster results if large_table will be converted back to myisam. so we've kept in myisam. We have reduced the table by 40 million rows recently though so I might benchmark it again though. Commented May 21, 2018 at 17:13
  • Have you tried file_per_table? Just a thought...
    – Vérace
    Commented May 21, 2018 at 17:38

1 Answer 1


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


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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