Saturday 28 January 2012

InnoDB vs MyISAM

MyISAM and InnoDB are the two most-common database engines available.  While installing the DB most of them don't even take the time to select a storage-engine and just accept the database default. 

The storage-engine is what which store, handle, and retrieve information for a particular table.

1. MyISAM

 It is the default engine with MySQL. Data in MyISAM tables is split between three different files on the disk.One for the table format, another for the data, and lastly a third for the indexes. 

2. InnoDB

 InnoDB is transaction-safe means data-integrity is maintained throughout the entire query process. InnoDB also provides row-locking, as opposed to table-locking, means when one query is busy updating or inserting a row, another query can update a different row at the same time.

InnoDB boasts is the ability to use foreign-key constraints. FK constraints allows developers to ensure that inserted data referencing another table remains valid.
MyISAM v/s InnoDB

   MyISAM in most cases will be faster than InnoDB. It is the default engine chosen by the MySQL development team. 

In  InnoDB features like row-level locking, transaction-safe queries, and relational table design are all very fast.

InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk.

No comments:

Post a Comment