How to Convert Table to InnoDB

From Brian Nelson Ramblings
Jump to: navigation, search

How to convert a MySQL Table to InnoDB

If you want to convert a MyISAM table to InnoDB, the process is fairly easy, but you can do something extra to speed things up. Before converting the table, adjust its order so that the primary key column is in order:

ALTER TABLE <table_name> ORDER BY 'primary_key_column';

This will pre-arrange the table so that it can be converted quickly without a lot of re-arranging required in MySQL.

ALTER TABLE <table_name> ENGINE=InnoDB;

InnoDB does not have a special optimization for separate index creation the way the MyISAM storage engine does. Therefore, it does not pay to export and import the table and create indexes afterward. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table.

If your table is large, then it may take a while to convert it over. There will probably be a fair amount of CPU usage and disk I/O in the process

Additional Articles