Differences between InnoDB and MySAM

 MyISAM is the usual alternative to InnoDB when choosing data storage technology in MySQL. These are some of the differences between the two:

Ø  InnoDB recovers from a problem by rerunning its logs, while MyISAM needs to go through all indexes and tables that have been updated and rebuild them if those changes have not been written to disk. The first process requires more or less the same time every time, while the second increases with the size of the database.

Ø  MyISAM leaves the operating system with the task of caching log reads and writes, while InnoDB performs the task itself, combining log and index caches. InnoDB does not directly send table changes to the operating system for writing, which can make it much faster than MyISAM in certain scenarios.

Ø  InnoDB physically stores records in the order of the primary key, while MyISAM stores them in the order in which they were added. When the primary key is chosen according to the needs of the most common queries this can mean a substantial performance improvement. On the other hand, if data is inserted in an order that differs substantially from the order of the primary key, InnoDB is forced to do a lot of reordering of the data to keep it in the proper order. 

Ø  InnoDB does not have the data compression that MyISAM enjoys, so both disk space and RAM cache can be larger. This problem has been reduced in MySQL 5.0, reducing it by approximately 20%.

ØWhen operating with ACID transactions, InnoDB must write to disk at least once for each transaction, although it can combine writes from multiple concurrent inserts. For typical hard drives, this limits about 200 transactions per second, so increasing this requires disk controllers with write cache and uninterruptible power supplies to maintain integrity. InnoDB offers several modes of operation that reduce this effect, but come with a loss of transactional integrity. MyISAM does not have that problem because it does not support transactions.