MyISAM to InnoDB?

[Rommeo]

Hello,

In one project I'm working on, all the tables' engine is MyISAM, and due to some reasons a friend of mine told me that InnoDB would be better in the project, so if I click operations in phpmyadmin and change myisam to innodb for all the tables, would it cause any trouble? (Would I have to change the queries etc?)

[Barand]

If you have on old version of MySql and you are using fulltext searches then you may have problem. If this is the case then you can still convert those tables not using fulltext. A mix of the two is allowed.

v5.6 has full text with InnoDB (not sure when it was introduced)

If you have no need of referential integrity and transactions you can leave them as MyISAM

Try it and see (taking a backup first).

[gizmola]

Great tips from Barand. 

You also will want to make sure that your InnoDB configuration parameters are set to empower InnoDB's architecture.  

In particular, InnoDB differs from MyISAM from a performance standpoint, largely because it has a data cache whereas MyISAM does not.  

Basically, when you do queries, the actual result data will be stored in this cache, and repeated queries can use the cache rather than reading the data from disk again.

Whether this is of benefit to you depends on your setup, and where you are running MySQL, as well as your ability to reconfigure MySQL to make use of it effectively, but for many small to medium size data sets, it is not unusual for people to have enough memory allocated to MySQL to keep the entire data set in cache 99% of the time.

This cache is called the InnoDB Buffer Pool and this article and calculator can help you figure out what you might be able to do.