MySQL supports many storage engines based on the data and application requirements. At times, the requirements are to support large amount read quires, while at other times, support transactions with more writes than reads. MySQL is designed to handle different type of requirements with different type of storage engines, each different from another with distinct strong and weak points. I have personally worked with InnoDB, MyISAM, MEMORY and MERGE storage engines, and they are the ones I will to cover. While most of it is good theory, and this article will cover the important points (benefits and problems) associates with each of the engines, I will also state my own experiences alongside.

MyISAM: This engine supports high read and write rates, and is best of high reads of large data. The data is stored in files on the disk. The important points to note about MyISAM are:

1. MyISAM does not support transactions and foreign key constraints.
2. It does not support row level locking. This can be a pain, because the entire table gets locked in case of inserts and updates. Readers obtain shared locks while writers obtain exclusive locks. Hence, it is better to split one large read statement into multiple small read statements whenever possible.
3. It comes with a handy tool myisamchk command line tool to check and repair corrupted tables.
4. It also comes with a nice myisampack utility which compresses MyISAM tables. Though the tables are no more available for writes, the reads become faster because of fewer disk seeks to get the records.
5. Indexes are simpler when compared to InnoDB
6. MyISAM has built-in full-text search.

My Experiences: I have found MyISAM a very robust engine, and easily holds 10 to 20 million records without causing any noticeable decrease in performance. Obviously, indexes play a huge role in the performance.

MyISAM is best used when there are large number of reads as against writes. This engine is not suitable for a huge number of writes.

InnoDB: InnoDB is ACID complaint storage engine of MySQL, and supports foreign key constraints. It boasts of automatic crash recovery and high performance as strong weapons from its arsenal. Important points:

1. Supports transactions.
2. It has row level locking feature, which means that it can support multiple writes and reads at the same time.
3. The index size tends to be large (because secondary indexes also contain primary index), and hence high amount of disk space is required when compares to MyISAM.
4. Altering highly indexed tables is a very expensive operation for InnoDB engine because of need to recreate indexes. But altering a table is something that is rare.
5. Because of complex index structure, the inserts and updates are somewhat slower than MyISAM (but this is easily compensated by support of multiple  concurrent writes due to row level locking).

My Experience: In my case, as long as the volume of data remained below 5 million, InnoDB was happy with inserts and selects, and very fast when compared to MyISAM. But as the data grew, inserts became slow due to multiple indexes, and complex selects slower.

MyISAM Merge Engine (MERGE): It is a combination of identical MyISAM tables into one virtual table. This is best used for logging and warehousing applications. It is best used with myisampack utility. Important points:

1. It is easy to repair multiple small tables in case of crash rather than one huge table.
2. It can use indexes of the original underlying tables, though the primary key or unique key constraints holds no meaning for a merge table.
3. Main problem comes when you want to alter the structure of the table. In that case, you need to alter all the underlying tables, otherwise the merge table breaks.
4. Index reads are slower.
5. The order of indexes in the MERGE table and its underlying tables should be the same.

My Experience: In my case, I had created one MyISAM table based on month, and current month table accepted data during inserts, while older tables were compressed with myisampack. The entire set was merged into a merge engine table to provide complete data set and faster writes. This model operated flawlessly some a long time, but afterwards, the number of tables to manage in case of an alter command became a pain.

MEMORY Engine: This engine stores the data in the memory, becoming very fast for storage and reads, but is not safe for sensitive data. A crash or restart of mysql will cause loss of data. Points:

1. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables.
2. MEMORY engine tables cannot have TEXT or BLOB columns, making them unsuitable for tables with such requirements. The best usage of this engine is for storing temporary session information.
3. There is a limit on the amount of data that can be stored in MEMORY engine tables. Hence, it becomes important to keep deleting older and obsolete data.

My Experience: I often use memory table to store data which can be recreated from another tables. For example, summary tables while displaying logs to the user. Summary tables are very handy, and are populated using external jobs. Because the underlying data is safely stored either in MyISAM or InnoDB engine, the risk of data loss is negligible. Because data is stored in memory, and is preprocessed most of the times, the lookup tends to be extremely fast.