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;
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.”
Yeah, we’ll see about that.
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.
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
Thanks for the tip, Drew.
Pingback: MySQL and PostgreSQL Full-text Searching - MILOL Blog
Pingback: MySQL | it
Cheers for the post. Worked perfectly!
Thanks.
thanks
Very helpful! I’m trying to resolve this problem for almost 5 hours. 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!