,

Storage Engines Types

Contents

MyISAM

  • Don't support transactions
  • Table-level lock (deals well with concurrent inserts)
  • Manages 2 files: data and index
  • MySQL 5 work with 8 bytes pointers and 256 TB of data per table. (MySQL 4: 4 bytes pointer and 4 GB per table)
  • Compress for read-only. If a normal table must be compressed, decompress it, alter and than compress it again
  • No crash-recovery system
  • Needs to be optimized once in a while
  • [Row_format]: dynamic, fixed and compressed
    • Dynamic: contains TEXT and BLOB fields in the table
    • Fixed: Fixed size fields only
    • Compressed: compressed tables


Merge

  • Type of DB engine that merges MyISAM identical tables into one big virtual table. Used for tables created dynamically that are separated because of it's high use but need to be read together sometimes.

InnoDB

  • Designed for transactions
  • Auto-crash recovery
  • It stores its data in a collection of files instead of only 2 like MyISAM
  • It can also use raw disk partitions to build it's table space
  • Very fast for primary key lookups but it doesn't compress the secondary keys. Not good if table has lots of keys
  • It might have concurrency problems
  • It doesn't need to be optimized
  • Before using this engine you should read "InnoDB Transaction Model and Locking"

Memory

  • Faster than MyISAM
  • Cannot change it's data
  • Don't support TEXT and BLOB
  • If data is to big, MySQL converts it into MyISAM

Archive

  • Use on insert and select only
  • Don't support indexes (until MySQL 5.0)
  • Compress all rows with zlib
  • Good for log tables
  • Designed for high speed inserting and compressed storage

Maria

  • Same as MyISAM but with row-lock, better BLOB handling and crash recovery