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.
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
All of these advantages far outweigh a drop in select speed.
The choice depends on various factors. In your specific case, since you need a relational design, you must use InnoDB .
A brief summary:
MyISAM
Greater speed in general when recovering data.
Absence of atomicity characteristics since it does not have to check the referential integrity, nor lock the tables to carry out the operations, this leads us, like the previous points, to a greater speed.
InnoDB
It allows to have the ACID characteristics (Atomicity, Consistency, Isolation and Durability: Atomicidad, Consistency, Isolation and Durability in Spanish), guaranteeing the integrity of our tables.
Data integrity, when the contents are modified with INSERT, DELETE or UPDATE statements, the integrity of the stored data can be lost in many different ways.
InnoDB recovers from unexpected system errors or restarts from its logs, while MyISAM requires a scan, repair, and index rebuild of table data that has not yet been flushed to disk.
In addition, it is likely that if our application makes a high use of INSERT and UPDATE, we will notice a performance increase with respect to MyISAM.
Some links of interest:
with MyISAM today the only difference that I have seen with respect to innodb is that you can make queries in (very long) text fields, referencing words that it contains, for example you can save a complete paragraph, and say that you bring the record that contains the words "head" within the paragraph and it will bring them, that is, the way in which it manages text-type fields, otherwise I always use innodb, before there were certain restrictions as they answered you above. I would use MyISAM in a table that stores, for example, the prologue of a book in one of its fields or something like that, and that needs to make it a search parameter.