Convert MyISAM in InnoDB for WordPress

Recently I migrated all MyISAM tables to InnoDB for some WP sites of clients.

I saw some improvement in mysql performance, specially when editing posts. I think earlier parallel write on

table were getting blocked because MyISAM do not support row-level locking.

Anyway, below is how I moved WPsites. 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

and

tables.

CREATE TABLE wp_posts_backup LIKE wp_posts ;
INSERT wp_posts_backup SELECT * FROM wp_posts ;
CREATE TABLE wp_postmeta_backup LIKE wp_postmeta ; 
INSERT wp_postmeta_backup 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’ 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.

SELECT concat('ALTER TABLE ', table_name, ' DROP INDEX ', index_name, ' ;')
FROM information_schema.statistics WHERE table_schema = 'NAME_OF_DATABASE' 
AND index_type = 'FULLTEXT' ORDER BY index_name ;

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:

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 ;

If result is empty, you can directly jump to next step.

MyISAM to InnoDB

Below is a syntax to change storage engine of

and

tables 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

SELECT concat('ALTER TABLE ', TABLE_NAME, ' ENGINE=InnoDB;')  
FROM information_schema.tables WHERE TABLE_SCHEMA = 'NAME_OF_DATABASE'
AND ENGINE = 'MyISAM' AND TABLE_TYPE='BASE TABLE' ;

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

content to verify if all rows are correct.
f

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