Tuesday, November 1, 2011

Major difference between two storage engine MyISAM and INNODB

The major differences between these two storage engines are :
  • InnoDB supports transactions which is not supported by tables which use MyISAM storage engine.
  • InnoDB has row-level locking, relational integrity i.e. supports foreign keys, which is not possible in MyISAM. MyISAM can do only a table-level lock.
  • InnoDB ‘s performance for high volume data cannot be beaten by any other storage engines available.
  • InnoDB offers ACID transcations, row level locking, foreign key constraints, automatic crash recovery, table compression (read/write).
  • MyISAM offers fast count(*), full text indexing, smaller disk footprint, very high table compression ( read only ).
  • innodb is journaled, and can recover from crashes where myisam can't, much like NTFS vs FAT file systems. 
  • In MyISAM the main mechanism used is the key cache. It only caches index pages from .MYI files
  • In InnoDB the main mechanism used is the InnoDB Buffer Pool. It caches data and index pages from InnoDB tables accessed.

No comments:

Post a Comment