Wait… hold on. Firstly, what are MyISAM and InnoDB?
MyISAM and InnoDB are the two commonly used MySQL engines for database tables. MyISAM is the default database engine for new tables created in MySQL 4 and 5.
So, why would I want to use one over the other?
Good question. Perhaps it is best answered by going through the differences between the two database engines.
It looks like there is no clear winner?
You’re right. Even though InnoDB is, as said, much more modern, both database engines still have their place in modern database structures. In fact, your choice should be dependant on how you anticipate the table will be used.
So, when would I want to use an InnoDB table?
If data integrity is important to you, multiple related InnoDB tables inherently take care of the data integrity between tables due to their support of foreign keys and enforcement of referencial integrity. Additionally, if your table will be used for intensive write operations (inserts and updates) the InnoDB engine can handle this much better than MyISAM, due to row level locking.
InnoDB does fall down however in the fact that it does utilise more memory than MyISAM and as previously mentioned it lacks full text indexing.
InnoDB sounds great. What need do I have for the MyISAM engine?
You don’t need to deal with referential integrity or building DBMS enforced relationships between tables with MyISAM, simply because the engine does not support it. One could argue this makes MyISAM tables quicker to create and useful for smaller, quicker tasks as well as initial system prototyping.
MyISAM also tends to be faster at many operations, especially large scale SELECT queries returned many results. For tables which will be used mainly for data retrieval with less in the way or inserts or updates, the performance of MyISAM may be a deciding factor. As well as this generalised speed, the MyISAM engine supports full-text indexing, which would be useful for large tables that contain textual data that needs to be fully searched and/or sorted on a regularly basis.
Okay. So, which would you suggest? Give me a quick summary.
InnoDB should be chosen where data is written reguarly and data integrity and security is critical.
MyISAM is the best choice for tables in which there is large amounts of data, especially textual data, that is read from significantly more than written to.
No problem. 🙂