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
Introspection.blog 