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 :<\/p>\n
Convert existing MyISAM to InnoDB table (quick and direct)\u00a0<\/strong><\/p>\n ALTER TABLE table_name<\/em> ENGINE = InnoDB;<\/p>\n Transfer Data from MyISAM to InnoDB table<\/strong><\/p>\n INSERT INTO innodb_table\u00a0<\/em>SELECT * FROM\u00a0misaim_table\u00a0<\/i>ORDER BY primary_key_columns\u00a0<\/em><\/p>\n 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\u00a0<\/em>to 80% of available physical memory for optimal performance.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/a><\/p>\n<\/div>","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20,2,15],"tags":[23,5,16],"yoast_head":"\n