MySQL gives the user several different storage engine options when creating a new table in a database. The two primary options for traditional databases are InnoDB and MyISAM. I won't go into the history of each engine, but I'd like to discuss when you would choose one versus the other. InnoDB has several features that MyISAM does not provide:
http://dev.mysql.com/doc/refman/5.0/en/innodb-overview.html
So why would anyone ever use MyISAM? First, it's been around longer and is very reliable. People are also more comfortable with the one file per table concept. And maybe most importantly it is the default engine when you create a new table. I've used both and like both, and when I was testing MySQL 5.0 over the last few days I decided to put both to several speed tests on a rather small table (45,000 rows). Each row contained about 470 bytes of data. Every test I ran showed that each database had similar performance. I was hoping that one would be superior to other but it just wasn't the case. On the other hand, my table size was pretty small. And I did discover that with large text based primary keys, MyISAM was much faster. But when the key size was reasonably small InnoDB had slightly better performance. I'm going to try to insert a tera-byte of data into each database and see which one performs better. I love all of the different options available for InnoDB storage that are available so it should be fun to try out different parameters and see which ones perform best.
