15 Dec

WordPress How To Convert Tables to Innodb

Innodb tables are tables within MySQL that should be used instead of MyISAM for numerous reasons, the very least is that their transactional nature means they will not corrupt if you system has to reboot or lose power.

One of the problems is to change your tables from MyISAM to Innodb you need to run the following command:
ALTER TABLE YourDB.wp_posts ENGINE=InnoDB;
Once per table and that means lots of manual typing

I found a script that will create the list of tables for you:
SELECT CONCAT( 'ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;' )
FROM information_schema.tables
WHERE ENGINE = 'MyISAM'
AND table_schema NOT
IN (
'information_schema', 'mysql'
)

Which outputs something like:
ALTER TABLE YourDB.wp_bad_behavior ENGINE=InnoDB;
ALTER TABLE YourDB.wp_commentmeta ENGINE=InnoDB;
ALTER TABLE YourDB.wp_comments ENGINE=InnoDB;
ALTER TABLE YourDB.wp_links ENGINE=InnoDB;
ALTER TABLE YourDB.wp_options ENGINE=InnoDB;

Which you can then cut and paste into any MySQL SQL window and run and all your tables are converted. The nice thing is if the script stops for some reason you can rerun the first script again without breaking anything.

Note – if you see ‘….’ at the end of the ‘ALTER TABLE’ lines this is phpMyAdmin truncating the output – turn on full text output and you’ll see the correct commans.

Leave a Reply

Your email address will not be published. Required fields are marked *