Convert MyISAM tables to InnoDB

May 22, 2013

Did you know that InnoDB replaces MyISAM as the default storage engine since MySQL 5.5.5? For this reason you may want to convert most (if not all) of your current MyISAM tables to InnoDB.

WordPress does for example make use of the default storage engine. That means old WordPress installations will make use of MyISAM if you don't update them to use InnoDB instead.

SQL to get the job done

This is the SQL to convert a single table:

ALTER TABLE `databasename`.`tablename` ENGINE=InnoDB;

You can generate this SQL for all tables in a certain set of databases by using the following query:

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') as query
FROM information_schema.tables
WHERE table_schema in ('db1', 'db2');

For a list of the tables that use MyISAM run the following query:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine = 'MyISAM';

The above query will show you tables in the databases information_schema and mysql and you may not care to see those since they are system databases that should not be tampered with. This query excludes them from the results:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema NOT IN('information_schema', 'mysql');

And why not combine it with the query generator while we are at it:

SELECT table_schema, table_name, CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') as query
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_schema NOT IN('information_schema', 'mysql');