Convert MyISAM tables to InnoDB with SSH

There are many benefits in converting your MySQL storage engine from MyISAM to InnoDB. Kinsta has a great write up on the topic: While you can do this manually per table, it’s not very practical if you have lots of tables to convert. The following commands will batch convert all MyISAM tables to InnoDB over SSH with WP-CLI. This is especially useful for large multisite networks.

wp db query "SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME, ' ENGINE=InnoDB;') FROM information_schema.TABLES WHERE ENGINE = 'MyISAM'" --skip-column-names > db_optimize.sql
wp db query < db_optimize.sql
rm db_optimize.sql

That’s it! 🏆Enjoy knowing that your database will be faster, use less server resources and be more recoverable.