Converting MySQL/MariaDB database tables from MyISAM to InnoDB
The InnoDB storage engine in MySQL/MariaDB is more performant than MyISAM – here are a few ways to change that database structure, using both the command line and also some WordPress plugins.
Changing from MyISAM to InnoDB via the command line
If you have wp-cli installed on your webserver you can check for MyISAM tables like so:
wp db query "SHOW TABLE STATUS WHERE Engine = 'MyISAM'" --allow-root
This will check if you have MyISAM tables and list them out – if there are none then there will be an empty return, but if you have some you can use the following to change them.
Take a backup of the database before you make the table changes, easy using wp-cli, from the webroot directory run:
wp db export
Once the back up is done you can run the wp-cli code below to make the storage engine change:
wp db query "$(wp db query "SELECT CONCAT('ALTER TABLE ',table_name,' engine=InnoDB;') FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema = '$(wp db size --format=csv --allow-root | awk -F, '{print $1}' | tail -1)'" --allow-root --silent --skip-column-names)" --allow-rootThe only time the above command will fail is if PHP CLI doesn’t have enough memory by which you can increase it and try again, I find that 256MB is a decent setting.
Thanks to the CloudKeeper for this code, they also have native MySQL code to use if you prefer using that then wp-cli.
Changing from MyISAM to InnoDB via phpMyadmin
The storage engine change can also be done via phpMyAdmin, select the table, click on Operations and change the storage engine from the dropdown.
Changing from MyISAM to InnoDB via a script
If you don’t have command line access and prefer not to use a plugin you can try a tried and tested script from interconnectit – it’s primarily a search and replace but also has other features such as changing the storage engine to InnoDB for all tables at once.
Changing from MyISAM to InnoDB via a plugin
Couple of WordPress plugin options here:
