tl;dr Always use InnoDB

MyISAM is a MySQL storage engine. I remember my first days when using MySQL that I didn't know what to choose, if MyISAM or InnoDB. If you searched on forums and alike you probably found comparsions between the two storage engine talking about performance, consistency, transactions, etc... What they didn't tell you is that InnoDB for almost all the use cases is the right choice.

Some of the limitations of MyISAM is that it does not support foreign keys. The funny thing, though, is that it silently accepts a declaration of a foreign key without creating it, causing a really bad sense of security if you belive you have referencial integrity when you really don't.

MyISAM is nontransactional. That means that it does not promises you to have a consistent state of your data after a crash. You'll have to run repair process to fix the problem in the datafiles in order to start the database again, process that can take hours on large databases. Of course it has a positive impact on performance, because you are giving up the safety of the information you are storing. If you willingly give up this security because you are reaching the limit of InnoDB, it's OK. But don't make the mistake of choosing MyISAM at first for speed reasons.

If you take backups snapshoting the FS probably your backups will be corrupt. It would be a shame that in case of need you find out that your backups are broken, also. Right? Doing a dump is the safer way when dealing with MyISAM. You can also use XtraBackup for the job.

While you can have database replication using different engines, you'll loose all the integrity checks whenn using MyISAM engine. Notice that the 'delete on cascade' policies will not be applied and inconsistent data could appear.

While MyISAM has text search, is not that good. And in InnoDB 5.6 was introduced full text search also, so this is no excuse! Be careful because older versions of InnoDB do NOT support text search and the table must be altered before changin the engine.

If I haven't convinced you yet, InnoDB is the default storage engine in MySQL 5.5 so thing about MyISAM as some legacy storage engine instead of a real option.

OK. If you want to migrate all the tables of the database from MyISAM to InnoDB you can follow this steps:

Ensure that InnoDB is enabled by running on the MySQL console:



SET @DATABASE_NAME = 'name_of_your_db';

SELECT  CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
ORDER BY table_name DESC;

Setting the @DATABASE_NAME with the appropiate value and this code will generate a SQL that has to be copy pasted again to a MySQL console. Beware! You are changing the engine of the database, this is a sensible operation. Please check that your backups are up and running before performing this action.

Usually no changes on the application side are required when changing the storage engine. You don't have a reason to switch to InnoDB!

Regards, Jan.