Storage Engines in MySQL

Storage Engines OR Database engines in MySQL

The storage engine or database engine in MySql Database is the underlying software componemt that DBMS(database management system) use for CROD(create, read, update and delete) Operations.

MySQL supports several storage engines like MyISAM, InnoDB, Memory, CSV etc.

We can determine which storage engines our server supports using SHOW ENGINES command.

The Support column display 3 values YES, NO, DEFAULT.

YES indicates that an engine is available.

NO indicates that an engine is not available.

DEFAULT indicates that an engine is available and currently set as the default storage engine.

Example:


mysql> SHOW ENGINES\G; 
*************************** 1. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 9. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
9 rows in set (0.00 sec)

MySQL supported storage engines some are following:

MyISAM:

MyISAM storage engine is one that is used the most in Web, data warehousing, and other application environments. MyISAM is default engine that Offers great performance for read heavy applications. MyISAM supports Full-text search indexes.

InnoDB:

InnoDB storage engine best suited for write heavy environments thanks to row-level locking. Offers good built-in recovery and solid data reliability. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.

Memory:

This engine was formerly known as the HEAP engine. Memory storage engine(All in-memory) is Very fast for data retrieval, however due to being stored only in memory. it is great for temporary tables but all data is lost on shutdown.

CSV:

CSV storage engine stores data in text files using comma-separated values format. we an use the CSV engine to easily exchange data between other software and applications that can import and export in CSV format.

The various storage engines provided with MySQL are designed with different use cases in mind. It is good to have an idea of the advantages and disadvantages of the various storage engines.

Tags