MySQL

How can I convert my MyISAM tables 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 tablename 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.

Then, simply change the table engine:
ALTER TABLE tablename ENGINE=INNODB;

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.
These statements are also safe in replicated environments. When you issue this statement to the master, it will begin the conversion process. Once it is complete on the master, the statement will roll down to the slaves, and they will begin the conversion as well. Keep in mind, however, that this can greatly reduce the performance of your configuration in the process.

Related Articles

  • LAMP STACK

    What is a LAMP Stack? “LAMP stack is a popular open source web platform commonly used to run dynamic web sites and servers. It includes Linux, Apache, MySQL, and PHP/Python/Perl...
  • Installing phpMyAdmin and connecting to remote database server

    phpMyAdmin is one of the many tools that allows administrators the ability to effectively and efficiently work with their databases. This can can either be a local database or on...
  • Monitoring MySQL

    Monitoring MySQL servers can be difficult in a way that a novice won’t be able to monitor MySQL that easily. MySQL gives a comprehensive list of variables to check your server’s health...
  • How to Display MySQL Storage Engines

    How to Display MySQL Storage Engines From the MySQL command line, issue the following command: SHOW ENGINES; You should receive a result similar to:   Typically the default storage engine...