MySQL: Convert a table from MyISAM to InnoDB or vica versa

This post just shows you the commands to convert a table from MyISAM to InnoDB or from InnoDB to MyISAM. If you don’t know the differences between these storage engines, you probably shouldn’t be changing this.

(tl;dr: row/table locking and full text search behaves differently, on top of a couple of hundred other parameters. If you’re worried about performance, make sure to check innodb_buffer_pool_size (InnoDB) and key_buffer_size (MyISAM) before and after your changes)

Convert a table to MyISAM

If you currently have a table in InnoDB and you’d like to convert it to MyISAM, use the following query. Please note that depending on the size of table, this can take a couple of seconds to a couple of hours to complete.

Consider running this in a screen and schedule downtime for your application.

$ mysql
MySQL> USE your_database_name;
MySQL> ALTER TABLE your_table_name ENGINE=MyISAM;
Query OK, 5 rows affected (0.25 sec)
Records: 5  Duplicates: 0  Warnings: 0

Convert a table to InnoDB

Alternatively, to convert to InnoDB follow these steps.

$ mysql
MySQL> USE your_database_name;
MySQL> ALTER TABLE your_table_name ENGINE=InnoDB;

Query OK, 5 rows affected (0.25 sec)
Records: 5  Duplicates: 0  Warnings: 0

This can take a while, depending on the size of your database.

How to check your current storage engine per table

Use the following query to see which storage engine your table us currently user. Each table in a database can use a different storage engine.

$ mysql
MySQL> USE your_database_name;
MySQL> SHOW CREATE TABLE your_table_name;

CREATE TABLE `your_table_name` (
  `name` varchar(20) DEFAULT NULL,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The last line will tell you which Engine that particular table is now using.

Need more reading material? Go for the official and well-documented explanation at dev.mysql.com: 14.5.4 Converting Tables from MyISAM to InnoDB.