MySQL Storage Engine / Table Type

It's only fair to share...Share on Facebook0Share on Google+0Tweet about this on Twitter0Share on LinkedIn0Pin on Pinterest0Digg thisShare on StumbleUpon0

There are various storage engine for mysql tables

  1. MyISAM
  2. InnoDB
  3. MERGE
  4. MEMORY (HEAP)
  5. ARCHIVE
  6. CSV
  7. Blackhole
  8. FEDERATED

MyISAM:

  • It is default table type for before mysql 5.5
  • Does not support transaction.
  • Its provide table-level locking

InnoDB:

  • It is default table type for after mysql 5.4
  • Fully support ACID-compliant and transactions
  • Support crash recovery, multi-version concurrency control and row-level locking.
  • Only InnoDB provides foreign key referential integrity constraint.

MERGE:

  • Its provides a way to combine many identical MyISAM tables into one logical table, which referred  as MERGE tables.

Memory:

  • Its create table in memory.
  • Provide table-level locking.
  • Does not support transaction.
  • It is ideal for creating temporary table.
  • Data for memory storage engine is lost when database restarted.

ARCHIVE:

  • Its allow to store large amount of data, into a compressed format to save spaces.

CSV:

  • Its store data in CSV files.
  • It can be easily integrated into other applications.

Blackhole:

  • Its not store data, and bsically used to distributes database design.
  • We can used Blackhole database for performance testing.

Federated:

  • Its offers the ability to sepearte MySQL server from many physical servers.
  • Local federated table stores no data but when we query data from a local federated table, the data is pull automatically from the remote federated tables.

Comparison Table

Comparision Table

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">