InnoDB Storage Engine | MySQL Storage Engine

InnoDB is a high-reliability and high-performance storage engine

The InnoDB storage engine maintains its own buffer area for caching data and indexes in main memory. InnoDB stores its tables and indexes in a table-space, which may consist of several files (or raw disk partitions). This is different from when compared to MyISAM tables where each table is stored using separate files.

InnoDB Supports:

  • ACID Model (Atomicity,  Consistency, Isolation, and Durability)
    • Atomicity – Transaction Commits, when all the changes are succeed. Transaction Rollback, when all the changes are undone.
    • Consistency – The database remains in a consistent state at all times (before & after transactions)
    • Isolation – Transactions do not interfere with each others, when other transaction in progress.
    • Durability – InnoDB protect the data from crashes & any other danger. It can be achieve through double buffer.
  • Row-Level Locking
    • The system of protecting a transaction from seeing or changing data that is being queried or changed by other transactions.
  • Foreign Key
    • The foreign key relationship is defined on one column in both the parent table and the child table.
    • If a row is deleted or its foreign key value changed, and rows in another table point to that foreign key value, the foreign key can be set up to prevent the deletion, cause the corresponding column values in the other table to become null, or automatically delete the corresponding rows in the other table.
  • FULLTEXT index
    • The special kind of index that holds the search index in the MySQL full-text search mechanism. Represents the words from values of a column, omitting any that are specified as stopwords.
    • Available only @ MySQL 5.6.4 or Above