myisam and innodb storage explained +

There is a lot of discussion among MySQL specialists about what MySQL engine you should choose. If you contact a consulting firm, they might advise you to switch to InnoDB, while others will defend ferociously the MyISAM engine. In this post, I will explain the advantages and disadvantages found in each engine, so you will be able to get a better grasp and be more prepared when you contact a professional.

First, let’s start with a comparison table:

 
So what do I use, MyISAM or InnoDB? As you can see from the above comparison, it is very important to realize that there is no simple answer to your question. Instead, ask yourself which storage engine is best suited for each part of your application.

The general guideline I use is: if you require multi-statement transactions, advanced isolation levels and row-level locking, foreign key constraints, or otherwise have a requirement for ACID features, go for InnoDB. Otherwise, simply use MyISAM, the default.

Sometimes MyISAM is used just because it is default. In other cases this is a deliberate choice with system being optimized to deal with MyISAM limits. In vBulletin for example, there is a dedicated slave available for all long reporting queries.

The concurrency control mechanism used by MyISAM works very well with tables accessed mostly using SELECT and INSERT statements. Basically, the MyISAM features which forbid moving to InnoDB are typically FullText Search and RTREE indexes/GIS with FullText being much more common.

With InnoDB, you can do clustering by primary key, caching data, higher concurrency and background flushes. On the other hand, you end up with significantly large tables (especially if data size is close to memory size), generally slower writes, slower blob handling, concurrency issues, problems dealing with very large number of tables and slow data load.

It is very important to make sure your application is ready to work with InnoDB. For example, you should be ready to deal with deadlocks which can happen with InnoDB even if you do not use transactions, but which are not existent with MyISAM.

It is also important to adjust processes as required to work with InnoDB. For example, copying the binary data of one of the databases from a server to another works great for MyISAM but does not work with InnoDB.

The TOP5 reasons why I prefer MyISAM, over InnoDB:

1. InnoDB maintains ACID compliance, MyISAM does not have that kind of overhead in its storage engine.
2. InnoDB Transaction Isolation causes data to be written in order to use multiple views. Opening MyISAM tables are independent of other tables.
3. InnoDB Key Processing is more involved than MyISAM.
4. Certain MyISAM optimizations to table design (ROW FORMAT=FIXED, PRIMARY KEY order) are somewhat neutralized if done to an InnoDB table due to the first 3 reasons mentioned. However, applying such optimizations will speed up InnoDB Data against not having such optimizations at all.
5. There are software alternatives (Searchlight) to substitute a MySQL query that generates table locks with MyISAM.

In conclusion, InnoDB maintains excellent data integrity at the cost of storage engine housekeeping. MyISAM, being free of such overhead, can be used in a concentrated way to perform lightning-fast SELECTs at the cost of data loss in the event of a crash.

I also want to highlight that Oracle purchased Innobase and its technology. By acquiring InnoDB, Oracle has gained control over a key technology of a potential competitor, the increasingly sophisticated MySQL database. This puts Oracle in a position where it could be able to influence (directly or indirectly) the future development of the MySQL database product.