Question:
When choosing the storage engine in MySQL, when should I use InnoDB and when MyISAM and what does the choice depend on? Is one superior to another?
In my case I have 8 tables, some with half a million records, others small, and all of them are related in some way.
Answer:
The only reason to use MyISAM in MySQL versions prior to 5.5 was that InnoDB did not support partitioning tables or creating FULLTEXT indexes.
Those restrictions no longer exist.
The only reason to use MyISAM is that it's faster to read and slightly faster to insert (en mass and measuring an isolated process), but that's because such operations perform a table lock.
If you are going to have multiple updates, or concurrent inserts, for example, a table-level lock is very inefficient , versus an update on an InnoDB table that uses row-level locking , and thus allows concurrent operations.
Add to all this that InnoDB allows the use of foreign keys that are essential to maintain the referential integrity of a data model, support for transactions.
In short, use InnoDB because
- In the current MySQL version it supports everything that MyISAM supports.
- Foreign Keys / Relational Integrity
- Row-level locking
- transactions
All of these advantages far outweigh a drop in select speed.