Things to remember when you migrate from MyISAM to InnoDB

Occasionally we have customers with MyISAM storage engine approaching us to migrate their database to InnoDB, MyISAM is great if you are just an application like web content management system with no multi-user concurrency challenges but what if you are building an highly transactional data web property ? InnoDB is much preferred for such situations, InnoDB provides Row-level locking (Oracle like) for consistent reads on an multi-user concurrent user high performance database application. InnoDB also guarantees maximum data integrity by supporting FOREIGN KEY, We captured below few interesting points to remember while migrating your database from MyISAM to InnoDB :

• Data of InnoDB tables is stored in *.ibd files, deleting those files will permanently corrupt your database
• InnoDB tables consumes more storage space than MyISAM tables .
• Unlike MyISAM, InnoDB is a transactional database engine. In any typical MyISAM environment, MySQL DBAs don’t worry much about COMMIT (to finalize the changes) and ROLLBACK (to undo the changes) statements.
• By default, InnoDB tables use setting autocommit=0 and this avoids unnecessary I/O when you are issuing long transactions with consecutive INSERT, UPDATE or DELETE statements, This allows you to issue a ROLLBACK statement to recover lost or garbled data if you make a mistake on the mysql command line or in an exception handler in your application .
• The reliability and scalability features of InnoDB require more disk storage than equivalent MyISAM tables. You might change the column and index definitions slightly, for better space utilization, reduced I/O and memory consumption when processing result sets, and better query optimization plans making efficient use of index lookups.
• InnoDB tables build your data on disk to optimize queries based on primary keys, Each InnoDB table has primary key index called clustered index that organizes the data to minimize disk I/O for primary key lookups.

Convert existing MyISAM to InnoDB table (quick and direct) 

ALTER TABLE table_name ENGINE = InnoDB;

Transfer Data from MyISAM to InnoDB table

INSERT INTO innodb_table SELECT * FROM misaim_table ORDER BY primary_key_columns 

We recommend indexes creation for InnoDB tables after loading data, loading data on indexed table will seriously impact performance. The same principle applies if you have UNIQUE constraints on secondary keys. Increase size of system variable innodb_buffer_pool_size to 80% of available physical memory for optimal performance.