Storage Engines
From Hashmysql
What is a "storage engine", and how do I decide which one to use?
A storage engine, also known as a "table type", is the engine which actually handles the data for your table(s). This is an alien concept for people coming from another RDBMS, and it is in fact a pretty blatant violation of one of Codd's rules for relational databases, but in MySQL you are able to choose which engine to use on a table by table basis, so that you are not paying the cost of transactional overhead if you do not need it, or so that you can have row-level locking available when you need it, etcetera.
- MyISAM
- This was the default storage engine until MySQL 5.1. It uses table-level locking (so is not always a good choice if you have a large number of concurrent reads and writes on the same table); it does not support foreign keys or transactions. It is an extremely fast engine aside from cases involving very large BLOB/TEXT columns or concurrent reads/writes, but is somewhat fragile if the server shuts down suddenly. Most of the
mysqldatabase tables use MyISAM. Tables are stored in a per-database directory in .MYD files, and their indexes in corresponding .MYI files.
- This was the default storage engine until MySQL 5.1. It uses table-level locking (so is not always a good choice if you have a large number of concurrent reads and writes on the same table); it does not support foreign keys or transactions. It is an extremely fast engine aside from cases involving very large BLOB/TEXT columns or concurrent reads/writes, but is somewhat fragile if the server shuts down suddenly. Most of the
- InnoDB
- This is the default storage engine since MySQL 5.5. It supports row-level locking (making it ideal for high concurrency of reads/writes), foreign keys and transactions, along with robust crash-recovery. Also performs better with large BLOB/TEXT columns. Uses a clustered index against the primary key (controlling physical order of data on disk). Until MySQL 5.5, all InnoDB tables were stored in a single system tablespace by default (typically a file called
ibdata1) in the data directory. Theinnodb_file_per_tablesetting allows you to override that behaviour. Since MySQL 5.6,innodb_file_per_tableis on by default.
- This is the default storage engine since MySQL 5.5. It supports row-level locking (making it ideal for high concurrency of reads/writes), foreign keys and transactions, along with robust crash-recovery. Also performs better with large BLOB/TEXT columns. Uses a clustered index against the primary key (controlling physical order of data on disk). Until MySQL 5.5, all InnoDB tables were stored in a single system tablespace by default (typically a file called
- NDB
- This is the storage engine for MySQL Cluster.
- HEAP or MEMORY
- These tables are stored in RAM (and thus the data will not survive a restart/reboot).
- BDB
- Berkeley DB.
