MySQL Engines: InnoDB vs. MyISAM

MySQL databases are 2 major storage engines InnoDB and MyISAM.

Following are the important differences of features and performance,
1. InnoDB is newer while MyISAM is older.
2. InnoDB is more complex while MyISAM is simpler.
3. InnoDB is more strict in data integrity while MyISAM is loose.
4. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
5. InnoDB has transactions while MyISAM does not.
6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
8. MyISAM has full-text search index while InnoDB has not.

InnoDB and MyISAM have their unique prons and cons against each other.

Prons of InnoDB
1. InnoDB should be used where data integrity comes a priority. (Because it inherently takes care of them by the help of relationship constraints and transactions.)
2. Faster in write-intensive (inserts, updates) tables.(Because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.)

Cons of InnoDB
1. More complex
2. Consumes more system resources such as RAM.
3. No full-text indexing.

Prons of MyISAM
1. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
2. Faster than InnoDB.
3. Full-text indexing.
4. Especially good for read-intensive (select) tables.

Cons of MyISAM
1. No data integrity (e.g. relationship constraints).
2. Doesn’t support transactions.
3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

In Short, InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.