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 is currently using. 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: Converting Tables from MyISAM to InnoDB .