Topic: MyISAM acting as InnoDB

[cwscribner]

Hi all.

I found a previous thread on here with someone asking about MyISAM vs InnoDB(I have this same question).  After reading through the thread, the basic conclusion was that even though MyISAM doesn't have direct support for foreign keys, you can manually set up MyISAM to enforce/cascade the updates and deletes.  Am I on the right track here or am I still missing some vital info?  And am I okay sticking with MyISAM even though I'm using foreign keys, or should I switch to InnoDB?

[ignace]

MySQL is great for most applications and it's very fast. They provide InnoDB for each application that requires some minimal data constraints (beyond indexes) on a database-level. When however your data-constraints become more and more important in an application, you'll find that MySQL is no longer your best option and most would switch to PostGreSQL. I'm no database expert of course. I'm just telling you what I've learned over the years.

[Mchl]

The difference is, when using MyISAM, you need to write your connstraint logic yourself, which has several drawbacks (you're likely to introduce bugs, it will be separated from database, etc..). With InnoDB, you're using well known and tested code (made in Oracle BTW ) to do same thing.

FK Constraints is not the most important difference between MyISAM and InnoDB. InnoDB is fully ACID compliant, which has several benefits: you can use transactions to avoid race conditions, InnoDB tables are crash safe (no more REPAIR TABLE).

InnoDB also seems to be getting more and more efficient with each new version, although the results will vary greatly depending on usage (mostly on writes/reads ratio for a table).