MySQL: How to change or convert MyISAM to InnoDB or vice versa?

by Yang Yang on September 2, 2009

After we have discussed the advantages and disadvantages of MyISAM and InnoDB, you might want to convert them back and forth and see the performance differences yourself. For small website, the difference may be minor but for a large popular application,

  • MyISAM tables yield better performance results in selecting data records
  • InnoDB tables give better performance results in inserting and updating data records

To change a MyISAM table to an InnoDB table:

ALTER TABLE table_name ENGINE = InnoDB;

To convert an InnoDB table to a MyISAM table:

ALTER TABLE table_name ENGINE = MyISAM;
Will Fastie October 24, 2009 at 9:48 pm

According to the MySQL documentaion at http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html, converting from MyISAM to InnoDB is an “unsupported operation.”

Yang Yang January 26, 2010 at 1:37 pm

Yeah, we’ll see about that.

Gokul July 19, 2012 at 4:41 pm

So is it OK to convert an InnoDB table to MYISAM? I’m trying to convert those tables since most of my tables are in MYISAM (consider this as syncing everything to standard). I need a concrete answer since my the tables contains very important data. Thanks.

Drew November 9, 2009 at 2:51 am

Hi Will,

According to the docs that limitation/safeguard/whatever is only for “system” tables “(such as user or host).”

http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html

Yang Yang February 26, 2010 at 6:15 pm

Thanks for the tip, Drew.

Matt Brotherson January 13, 2011 at 5:31 am

Cheers for the post. Worked perfectly!

Sinan E. November 19, 2011 at 12:25 am

Thanks.

beny February 29, 2012 at 10:45 am

thanks

armand May 30, 2012 at 9:52 pm

Very helpful! I’m trying to resolve this problem for almost 5 hours. Thanks!

alex September 25, 2012 at 4:55 pm

can one do this online – whilst data is being read/written to the table you’re converting / i.e. is it doing a in-place conversion or via a temporary table, then once all is converted replace the existing “old” table ?

Francisco Barcenas Cruz November 9, 2012 at 11:11 am

Great but how do you do that for all the tables in the database at one shot. I have 300+ tables in mine. I’m not doing them one by one.

pdflog January 5, 2013 at 1:12 pm

I’m using innodb engine for database of my wordpress blog with more than 400k posts. Now its very slow for a SELECT LIKE query for wp_post table. is it safe to convert only wp_post table to myisam or should convert all tables?

Mario Salazar January 25, 2013 at 7:54 am

It´s complicated.
I use webmin to make a backup of the whole database.
Edit the backup file using vi and change the sentences using search and replace
:%s/ENGINE=MyISAM/ENGINE=InnoDB/g
save the file
into the database execute the backup
thats all until your database is huge.
vi is fassst

TiredOfMysql January 30, 2013 at 10:15 am

If you were a girl I would kiss you!!
I had 3 freelancers in the last 3 weeks to sort out my database, and nobody could sort it out! I even increased my RAM to 32GB (normally the website runs on 4-8GB)!!!
For Joomla users: change only jos_content table (or whatever table is being used for read/write) and that’s it. I have a few thousands of full articles that are written into the table everyday and read by thousands of people simultaneously.
Thank you!

Comments on this entry are closed.

{ 2 trackbacks }

Previous post:

Next post: