[Database]: storage engine [part1]

“have ever heard ‘storage engine’? what is that?”
“do you know what is MyISAM and InnoDB?”
“and what are the differences between them?”

before having a discussion about MyISAM and InnoDB let we talk about ‘database engine’ [or storage engine]. a storage engine is an underlying software [in Database Management System – DBMS] which takes control in managing how to connect, create, alter and remove database objects [tables, triggers, indexes, and others]. the storage engine also takes care of transaction.

“so what MyISAM is?”
MyISAM is one of popular storage engine, it’s a replacement of ISAM storage engine.
MyISAM developed for MySQL DBMS, MyISAM storage engine will create three type of file for every table creation, FRM file [store the table definition], MYI file [store all indexes for the table] and MYD [MY Data, store the records].

“then what about InnoDB?”
InnoDB is also a storage engine for MySQL, invented by Innobase Oy [Finland], an open source company. InnoDB is dual licensed, GNU GPL and as a proprietary software [owned by Oracle – Innobase Oy was acquired by Oracle in 2005].

these are some differences between InnoDB and MyISAM:

  • InnoDB uses row-level locking while MyISAM uses table-level locking, InnoDBB has a better performance in update action,
  • it store the table definition, indexes and all of the rows in one file, it’s unlike MyISAM,
  • a crashed table may be repaired by tracking the log when MyISAM table needs to be full scaned,
  • in the storage [physical disk], InnoDB places records in order of primary key [index] while MyISAM in order of their added time,
  • InnoDB supports transaction and better table relationship when MyISAM doesn’t support transaction.

there are still many storage engines out there like Memory, Federated, and others…
for further information about MySQL’s storage engine, you can find here: http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

have a good day…