MyISAM Storage Engine
MyISAM was the default MySQL storage engine prior to the MySQL server version 5.5.5. The current default is the InnoDB storage engine. The mysql database contains tables in the MyISAM format.
Each MyISAM table is represented by three files:
- Formatfile: Stores the definition of the table structure (mytable.frm)
- Data file: Stores the contents of table rows (mytable.MYD)
- Index file: Stores any indexes on the table (mytable.MYI)
The MyISAM storage engine stores each table on disk in three files (.frm, .MYD, and .MYI) and has the following features:
- Support for FULLTEXT searching and spatial data types
- Flexible AUTO_INCREMENT
- Compressed, read-only tables, which save space
- Table-level locking to manage contention between queries
- Portable storage format
- Ability to specify the number of rows for a table
- Ability to control the updating of non-unique indexes for loading data into an empty table
Additional features:
- MyISAM tables take up very little space.
- The table storage format is portable, so table files can be copied directly to another host and used by a server on that host.
- When loading data into an empty table, updating of non-unique indexes can be disabled and then re-enabled after loading the data.
- MyISAM supports geometric spatial extensions
- MyISAM can improve performance by limiting table size to a certain number of rows.
MEMORY Storage Engine
The MEMORY storage engine creates tables with contents that are stored in memory, represented on disk by a .frm file. Contents do not survive a restart of the server (the structure survives, but the table contains zero rows). MySQL manages query contention by using table-level locking. Deadlock cannot occur.
It has the following features:
- Table data and indexes that are stored in memory
- Very fast performance due to in-memory storage
- Fixed-length row storage format
- Table contents that do not survive restart
- Maximum size option –max-heap-table-size
- Table-level locking
MEMORY tables:
- Cannot contain TEXT or BLOB columns
- Can use different character sets for different columns
HEAP may still be seen in older SQL code, and the MySQL server still recognizes HEAP for backward compatibility. MEMORY performance is constrained by contention resulting from single-thread execution and table-lock overhead when processing updates. This limits scalability when the load increases, particularly for statement mixes that include writes. Also, MEMORY does not preserve table contents across server restarts. MEMORY is a valid and useful storage engine and should be considered with almost any application design to improve performance and to meet the needs of specific business rules.
ARCHIVE Storage Engine
The ARCHIVE storage engine is used for storing large volumes of data in a compressed format, allowing for a very small footprint. It has these primary characteristics:
- Represented by .frm file
- Data file: .ARZ
- Does not support indexes
- Supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE
- Supports ORDER BY operations and BLOB columns
- Accepts all but spatial data types
- Uses row-level locking
- Supports AUTO_INCREMENT columns
BLACKHOLE Storage Engine
The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. This storage engine has these primary characteristics:
- Represented by .frm file
- Used for replication
- Supports all kinds of indexes
- Retrievals always return an empty result.
- Verification of dump file syntax
- Measurement of the overhead from binary logging
- “No-op” storage engine that can be used for finding performance bottlenecks not related to the storage engine
- Transaction-aware
Committed transactions are written to the binary log, and rolled-back transactions are not.