When choosing the storage engine in MySQL, when should I use InnoDB and when should 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 are related in some way.
The only reasons to use MyISAM in versions of MySQL 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 is faster to read and slightly faster to insert (in bulk and by measuring an isolated process), but that's because such operations do 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 therefore allows concurrent operations.
Add to all that InnoDB allows the use of foreign keys that are essential to maintain the referential integrity of a data model, the support for transactions.
In short, use InnoDB because
- In the current version MySQL supports everything that MyISAM supports
- Foreign Keys / Relational Integrity
- Row-level locking
All these advantages far outweigh a decrease in speed of the select.