When I started out , I was made aware of only a couple of table types (now called storage engines‘) in MySQL since they were used frequently. At some point of time my curiousity grew & I learnt that MySQL offers a whole variety (though they are for highly specialized uses only!).So here goes.

The 10 MySQL Storage Engines :

1. MyISAM

  • Default storage engine.
  • Supports Full Text Indexing.
  • Table level locking.

2. InnoDB

  • Row level locking.
  • Transaction-safe (ACID compliant) storage engine.
  • Occupies more space than MyISAM tables.
  • Used in sites having large data while ensuring high performance.

3. Memory/Heap

  • Memory tables are storied in memory.
  • Use of hash indexes by default making them very fast.
  • Useful only for temporary tables since server shutdown causes loss of all data.

4. Merge/MRG_MyISAM

  • Collection of identical MyISAM tables.
  • Identical means that the underlying tables should have same columns,indexes,same order of columns/indexes,column type etc.
  • Useful for speed,reparing,managing data in big tables  broken into smaller parts & ‘merged’ .
  • Reads from the merged table are slower than individual reads.

5. Archived

  • Useful for storing large amounts of data  in a very small footprint since rows are compressed.
  • Indexing is not supported.
  • Reading is slow since a complete table scan occurs.

6. Federated

  • Used to access data from a remote MySQL database on a local server without using replication or cluster technology.

7. Blackhole

  • It acts as a ‘black hole‘  that accepts data but throws it away and does not store it.
  • Useful for Master-Slave configurated servers  to reduce the amount of data being transferred between master and possible many slaves.
  • Used to check the query syntax alone while not touching the physical records of the mysql engine.

8. CSV

  • Storage of  data in text files using comma-separated values format.
  • Indexing is not supported.
  • This format can be read, and even written, by spreadsheet applications such as Microsoft Excel or StarOffice Calc.

9. Example

  • Stub engine that does nothing.
  • Useful for developers who wish to learn how to begin writing new storage engines.
  • Indexing is not supported.

10. NDBCLUSTER

  • Used to implement tables that are partitioned over many computers.

note : The storage engines ISAM & BDB(Berkeley DB) are deprecated now.


Ref :
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
Daniel Schneller –  Explanation of Blackhole Storage Engine


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s