Monday, August 1, 2016

Nutshell of MyISAM and InnoDB

InnoDB:
 - ACID compatibility
 - Row Level locking for updates
 - FK and indexes support
 - Rollback and Undo Logging mechanism
 - Crash recovery support
 - non locking reads increases multi user concurrency and performance
 - Data and index stores together in one file. (tablename.ibd, tablename.frm)

MyISAM:
 - Only Full table locking. Table level locking
 - Full text search indexes
 - No transactions. No crash recovery.
 - Fast Count(*).
 - More compact in size on disk as compared to InnoDB.
 - tables could be further highly reduced in size by compressing with myisampack if needed, but become read-only.
 - MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
 - Can use MyISAM, if the table is more static with lots of select and less update and delete.

No comments:

Post a Comment