Convert MyISAM to InnoDB

Recently we migrated all MyISAM tables to InnoDB for most of our sites.

We saw some improvement in mysql performance, specially when editing posts. I think earlier parallel write onwp_posts table were getting blocked because MyISAM do not support row-level locking. There might be more logic to it but I am no database expert to comment on it.

Anyway, below is how we moved our WordPress sites. You may find process useful.

Important: Backup first, proceed later!

Backing up a table in SQL

I know I remind you to backup. Just in case you missed it, below is a quick way to backup a table MySQL itself. This may come handy if you are playing on live site.

Run following two commands to backup wp_posts and wp_postmeta tables.

CREATE TABLE wp_posts_BAK LIKE wp_posts ; 
INSERT wp_posts_BAK SELECT * FROM wp_posts;

CREATE TABLE wp_postmeta_BAK LIKE wp_postmeta ; 
INSERT wp_postmeta_BAK SELECT * FROM wp_postmeta;

Drop Fulltext Indexes

If you are using a plugin like YARPP, you need to drop fulltext indexes. If you proceed without dropping fulltext indexes, you will get an error going ahead.

Below are commands to drop YARPP’s fulltext indexes.

ALTER TABLE wp_posts DROP INDEX yarpp_title;
ALTER TABLE wp_posts DROP INDEX yarpp_content;

For all tables in ONE database

Below is a query to help you find all fulltext indexes on all your mysql tables for database db_wordpress.

mysql -e "SELECT concat('ALTER TABLE ',TABLE_NAME,' DROP INDEX ', index_name, ' ;')
FROM information_Schema.STATISTICS 
WHERE table_schema = 'db_wordpress' 
AND index_type = 'FULLTEXT' ORDER BY index_name " | tail -n+2 > drop.sql

For all tables in ALL databases

Alternatively, if you wish to drop indexes for all tables from all databases (except mysql database itself), you can use following query:

mysql -e "SELECT concat('ALTER TABLE \`',TABLE_SCHEMA,'\`.',TABLE_NAME,' DROP INDEX ', index_name, ' ;')
FROM information_Schema.STATISTICS 
WHERE TABLE_SCHEMA != 'mysql' 
AND index_type = 'FULLTEXT' ORDER BY index_name " | tail -n+2 > drop.sql

After you run above query, check drop.sql content to verify if all rows are correct. If drop.sql is empty, you can directly jump to next step.

If all looks good, run following to drop all fulltext indexes in one go:

mysql -f db_wordpress < drop.sql

For all databases version use:

mysql -f < drop.sql

MyISAM to InnoDB

Below is a syntax to change storage engine ofwp_posts and wp_postmetatables to InnoDB.

ALTER TABLE wp_posts ENGINE=InnoDB;
ALTER TABLE wp_postmeta ENGINE=InnoDB;

For all tables in ONE database

If you want to covert all your MySQL tables, then run a command like below on database db_wordpress

mysql -e "SELECT concat('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')  
FROM Information_schema.TABLES 
WHERE TABLE_SCHEMA = 'db_wordpress'  AND ENGINE =  'MyISAM'  AND TABLE_TYPE='BASE TABLE'" | tail -n+2 > alter.sql

For all tables in ALL databases

Alternatively, if you wish to covert all tables from all databases (except mysql database itself), you can use following query:

mysql -e "SELECT concat('ALTER TABLE \`',TABLE_SCHEMA,'\`.',TABLE_NAME,' ENGINE=InnoDB;')  
FROM Information_schema.TABLES 
WHERE TABLE_SCHEMA != 'mysql' AND ENGINE = 'MyISAM'  AND TABLE_TYPE='BASE TABLE'" | tail -n+2 > alter.sql

After you run above query, check alter.sql content to verify if all rows are correct. If alter.sql is empty, you probably do not have a table using MyISAM engine.

If all looks good, run following to convert all mysql tables to InnoDB.

mysql -f db_wordpress < alter.sql

For all databases version use:

mysql -f < alter.sql

Troubleshooting:

Most likely you will not need to troubleshoot anything. Still, if you get following error:

ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 767 bytes InnoDB doesn’t allow primary key wider than 767 bytes

Then you need to change primary key column for that mysql table. Most likely you did not specified any primary key and by default InnoDB picks first column as primary-key. You can add an auto increment column and set it as primary-key and then retry running MyISAM to InnoDB conversion.