Changing MySQL Database Storage Engine from MyISAM to InnoDB
MySQL is a popular open source database that is being used mostly in web applications together with PHP. Storage engine of a MySQL database is a factor for getting optimum database performance. I installed a MySQL database for web application development on a test computer server. The performance was kind of slow. I was thinking of converting the MySQL storage engine from InnoDB to MyISAM which looks more appropriate for the web application.
There’s a little difference between those two storage engine. A closer look will reveal that row locking support is the only advantage of InnoDB over MyISAM which on the other hand supports table locking. I would like to test out which of the two storage engine is better for my application.
MySQL creates a table using the default storage engine, usually MyISAM. It’s good that you can change from one MySQL storage engine to another, if it is inevitable. The ALTER TABLE statement could be used to convert a table from one storage engine to another via phpMyAdmin or via the mysql> prompt through ssh.
Example of the ALTER TABLE statement of changing from InnoDB to MyISAM:
ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;
The above statement would change your MySQL storage Engine. Dont’ forget that it takes a lot of computer resource to convert large tables. It’s best to plan out from the start which table type is most appropriate.
You could get more information about MySQL storage engines from the following site:
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
Posted on
Wednesday, February 3rd, 2010 at 9:46 am under 