29.8. Other Storage Engines

MySQL Server supports several other storage engines in addition to those already covered. This section summarizes them briefly, although you are not expected to know about them for the exam.

The BDB storage engine provides transactional tables. Each BDB table is represented on disk by an .frm format file and a .db file that stores data and index information. BDB supports transactions (using the SQL COMMIT and ROLLBACK statements) with full ACID compliance. The BDB engine provides auto-recovery after a crash of the MySQL server or the host where the server runs. BDB uses page-level locking. This locking level provides concurrency performance that is intermediate to that of row-level and table-level locking. It's possible for deadlock to occur.

The ARCHIVE storage engine provides an efficient way to store large amounts of data when you don't need indexes and need to minimize the amount of disk space used. This engine supports only SELECT and INSERT operations. SELECT scans the entire table, and INSERT performs compressed inserts. (Written records are cached until a read occurs, and then they are compressed and flushed to disk. This is done because you get better compression if you compress several rows at a time rather than individually.) To analyze and recompress an ARCHIVE table, use the OPTIMIZE TABLE statement. Each ARCHIVE table is represented on disk by an .frm format file, an .ARZ data file, and an .ARM metadata file.

The CSV storage engine stores records as text in the well-known comma-separated values format. This format is highly portable. The CSV engine does not support indexing. Each CSV table is represented on disk by an .frm format file and a .CSV plain text file that contains data rows.

The BLACKHOLE storage engine creates tables that act as "black holes." That is, what goes in does not come out. Data stored in a BLACKHOLE table disappears because the engine simply discards it. The only disk file associated with a BLACKHOLE table is its .frm format file.

The EXAMPLE storage engine does nothing except create tables. You can't even store any rows in an EXAMPLE table, although this is by design: The purpose of this engine is to provide simple example code in MySQL source distributions that demonstrates how to get started writing a new storage engine. The only disk file associated with an EXAMPLE table is its .frm format file.

The ISAM storage engine is an older engine that has been superceded by MyISAM. It uses an older table format that is obsolete because MyISAM offers better features and performance. ISAM is no longer available as of MySQL 5.