Take the 2-minute tour ×

When my database was originally created it was all created as MyISAM (for no particular reason) - over time as new tables have been added, they have all been InnoDB as this seems to be the PHPMyAdmin default.

It has never caused any issue that I am aware of but I really need to start thinking about performance optimisation especially as the application and database gets busier so want to change them all to one type and have opted for InnoDB - I am happy with how to make the change etc but my question is what, if any, implications this could have for the front end application? The application is a custom coded PHP application basically just doing normal CRUD operations so nothing special or out of the ordinary.

What should I look out for after the migration and is there anything likely to stop working or to need to be changed in the application as a result? I appreciate InnoDB needs customisation especially to get the best performance and am happy to look into that but just want to get them all changed first, running for a few days and then move onto the next thing.


share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

As with any such architectural change, the only way to be sure how it affects your application is to test it out. That is, on a test machine, not for your production website.

The caveats of switching from MyISAM to InnoDB are very few. You should tune for InnoDB of course. See for example my presentation http://www.slideshare.net/billkarwin/mysql-55-guide-to-innodb-status for some tips.

Also remember if you have allocated a large amount of RAM for the MyISAM key_buffer_size config variable, that will no longer be needed if you switch everything to InnoDB. I have audited quite a few sites that still have 4GB+ allocated to this buffer that is no longer used.

As for any changes you need to do in your application, no, there shouldn't be any need. InnoDB supports the same SQL data types and query semantics as MyISAM, except for a few exotic cases.

For example, MyISAM supports compound primary keys where the auto-inc column is second. InnoDB requires that the auto-inc column is the first column in the PK.

share|improve this answer
add comment

Your Answer


