Mysql – Will a change from MyISAM to InnoDB require SQL query changes in MySQL

innodbmyisamMySQL

I am planning to change some large MyISAM tables in my MySQL database into InnoDB.

This is because I am having performance problems where large and complex updates (updating a few million rows in one go) are stopping users from concurrently accessing that tables.

I understand that if I convert the table to InnoDB then the writes/updates will cause row locking rather than table locking.

If I do this, Will have have to review all my SQL selects/insert statements, and my stored procedures, and make any changes due to the engine change, or will they all 'just work'?

Best Answer

I don't think you need to change any SQL statements and SP's, They should work as they were before.

You need to keep in mind some points

  1. If you have MySQL version 5.5 or below and you have FULL TEXT index on Table then this is a issue, InnoDB doesn't support FULL TEXT index up to MySQL 5.5, They are supported in MySQL 5.6 and above.

  2. If you have Spatial Indexes on table, They are yet not supported in InnoDB engine

There are a lot of other points that you should keep in mind are, Please must have a look at some great links which suggest what are various things you should consider in migrating table from MyISAM to InnoDB

  1. What are the main differences between InnoDB and MyISAM.?

  2. How do you tune MySQL for a heavy InnoDB workload. ?

  3. Should I move to InnoDB during a planned migration. ?

  4. Online conversion from MyISAM to InnoDB ?

  5. Converting Tables from MyISAM to InnoDB.

Related Question