Selecting the Right Database Engine for MySQL

Choosing the right storage engine for MySQL is based on the following criteria

1. Transactions and Concurrency
2. Backups
3. Special Features

Transactions and Concurrency

1) If the application requires high transactions and high read/write concurrency, then InnoDB is the most suitable one. A good example of high transactions is Order processing applications, something like, Online Stock quotes.

2) If the applications requires high transactions but moderate read/write concurrency, then either BDB or InnoDB shall work fine.

3) If the application doesn't require transactions and  primarily issues SELECT or INSERT/UPDATE queries, MyISAM is a good choice. Many web applications fall in this category. Good examples are student database, jobs, acutions, realestate postings - there are more reads than writes to these applications.

Backups

Based on the frequency of backups, the storage engine selection can vary.

Special Features

Not all storage engines may provide a quick answer to a query like the following

select count(*) from itable;

MyISAM is the answer for this type of fast row counts. Innodb must count up all the rows actually, but the MyISAM  storage engine always knows the exact row count of a table without needing to do any work.

If the application requires referential integerity with foreign keys, then InnoDB is the best.

For full text-search capabilities, MyISAM is the only answer.