MyISAM vs. InnoDB

[johnnyb]

Hi,

I've been having some problems with the .myi files, (the index files), of my MyISAM tables dissapearing. It can be fixed by restarting the server but I cannot figure out what is causing the problem.

So, in looking for a solution I started reading about different table types and realized that I will not be able to use MyISAM tables for some transactional stuff that I am about to build.

This led me to wonder - should I just change all my tables to InnoDB - I'll be able to do transactions and maybe I won't have problems with .myi files dissapearing!

So, here's my dilemma - the MySQL manual is contradictory about why I should not use InnoDB tables - in one place it says it's slow because it's transactional, but in another it says it is the fastest disk-based engine in existence.

Finally the real question: Is there any reason I should not switch to all InnoDB tables? Are they really super fast yet support both transactions and foreign key constraints?

Thanks in advance for the advice - I'll check back later.

JB

[Kiwi]

Yes.

MyISAM tables do not support most features that make a database a relaitonal database. They are speed-optimised look-up tables, but you have very limited capacity to enforce structure and rules on the data beyond simple formatting.

This is going to continue: most of the relational features of SQL are implemented (or planned) for InnoDB tables. These include foreign keys, transactions, and triggers/rules.

You do take a performance hit with InnoDB tables: once mySql starts actually doing proper SQL processing, it's speed drops to comparable with Oracle and other fully features database engines. It's still fast, but not as fast.

If you only need simple look-up tables, MyISAM is fine. But if you need a relational database, you have to go to InnoDB.

[johnnyb]

Excellent. That's what I was thinking. I'll want to use triggers when they become available too, so if InnoDB is where it's all happening I'll get over there.