Categories
SQL / MySQL Tips and Tutorials

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

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;

By Yang Yang

Hello, I'm Yang. I build online businesses that please people. Want to join in and post some useful articles on Kavoir.com? Shoot me a message.

16 replies on “MySQL: How to change or convert MyISAM to InnoDB or vice versa?”

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.

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 ?

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.

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?

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

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 are closed.