Switch from MyISAM to InnoDB engine in MySQL

MySQLHello,

If, I as do, you have MyISAM tables (default storage engine until 5.5.5) and you want to switch them to InnoDB,  because you plan to update to MySQL >= 5.5.5, you will be able to switch but be careful with indexes !

/!\ Deleting or adding index and changing tables engine can be a risky and long operation regarding to the configuration of your MySQL, the size of the considered table, hardware configuration and of course the context ( for instance production environment ). Before changing the engine  you have to be sure that regarding to the usage of your table it is the good choice to make /!\

If after these warning, you still want to change your table engine and recreate indexes, here is a “simple” method.

  • First solution : directly switch the engine

Vérifiez d’abord les index que vous avez avant de les supprimer car le fonctionnement des index en InnoDB est différent. un travail de réflexion sur les index doit être fait en amont afin de bien mesurer l’impact d’une telle opération.

First check existing indexes on your table before deleting them because InnoDB index are different from MyISAM. A complex work on indexes has to be done to choose the best option for new indexes:

SHOW INDEX FROM your_table;

Delete index(es)  :

ALTER TABLE your_table drop index index_1;

Change the engine :

ALTER TABLE your_table ENGINE=InnoDB

Create a new indexes :

ALTER TABLE your_table ADD INDEX `index_1` (`column1`,`column2`,`column3`,`column4`);

Here we are, simple isn’t it ? Yes if you master indexes, MySQL configuration, engines . . .I’m not but I’m tryin to improve my knowledge! Be careful to understand effects that can result with a modification like that.

  • Seconde solution : create a clone of your MyISAM table
SHOW CREATE TABLE your_table \G

Then create the InnoDB table changing the “ENGINE=MyISAM” to “ENGINE=InnoDB”. You may delete indexes or not because they will maybe not fit to the new InnoDB engine.
Insert into your new table the data :

INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns.

After that you can create new indexes.

ALTER TABLE your_table ADD INDEX `index_1` (`column1`,`column2`,`column3`,`column4`);

Here is it for the second solution !

A various of different conditions have to be taken into account before making that kind of operation. MySQL parameter configuration will probably have to be changed after changing engine, the storage space will increase ( InnoDB > MyISAM) and memory too.

For more information about switching engine from MyISAM to Innodb you can check the appropriate documentation for MySQL 5.6 or MySQL 5.5

See you soon !

Etienne

PS: If you think that this article is not precise enough or contain errors, don’t hesitate to contact me : contact@etiennegautier.net

Tags: , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.