I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB).
The theories say:
- Both work with BTree indexes, but MyISAM can work with FULLTEXT index also.
- InnoDB is more strict in data integrity while MyISAM is loose.
- InnoDB has transactions while MyISAM does not.
- InnoDB has foreign keys and relationship contraints while MyISAM does not.
- MyISAM are faster to read but slower to write (I'm not sure about this one).
When I create a table, should I always sacrifice something if I want to keep integrity and I want to speed up the query making search by "text"?
How do you decide which engine to use if you need
- integrity ?
- speed ?
- constraints ?
- search by text ?
- transactions ?