When choosing the MySQL storage engine, when should I use InnoDB and when MyISAM and what does the choice depend on? Is it any higher than another?
In my case I have 8 tables, some of half a million records, other 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 partition tables or create 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 mass and measuring an isolated process), but that is because such operations effect a table lock.
If you are going to have multiple updates, or concurrent entries, for example, a table-level block is very inefficient , versus an update on an InnoDB table that uses blocking at the level of row , and therefore allows concurrent operations.
Add to all that InnoDB allows the use of foreign keys that are indispensable to maintain the referential integrity of a data model, the support for transactions.
In summary, use InnoDB because
All these advantages far outweigh a decrease in speed of the select.
The choice depends on several factors. In your specific case, when 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 do referential integrity checks, nor block the tables to perform the operations, this takes us as the previous points to a higher speed.
InnoDB
It allows to have the characteristics ACID (Atomicity, Consistency, Isolation and Durability: Atomicity, 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 failures or reboots from its logs, while MyISAM requires a scan, repair and reconstruction of indexes of data from tables that had not yet been uploaded to disk.
Furthermore, it is likely that if our application makes high use of INSERT and UPDATE we will notice an increase in performance with respect to MyISAM.
Some links of interest:
with MyISAM nowadays the only difference I have seen with innodb is that you can make queries in text fields (very long), referring to words that contain the same, for example you can save a complete paragraph, and say bring the record that within the paragraph contains the words "head" and bring them, ie the way you manage text type fields, otherwise always use innodb, before there were certain restrictions as you answered above. I would use MyISAM in a table that saves for example the prolog of a book in one of its fields or something like that, and that needs to make it a search parameter.