14.12 The ARCHIVE Storage Engine

The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.

Table 14.13 ARCHIVE Storage Engine Features

Storage limitsNoneTransactionsNoLocking granularityTable
MVCCNoGeospatial data type supportYesGeospatial indexing supportNo
B-tree indexesNoT-tree indexesNoHash indexesNo
Full-text search indexesNoClustered indexesNoData cachesNo
Index cachesNoCompressed dataYesEncrypted data[a]Yes
Cluster database supportNoReplication support[b]YesForeign key supportNo
Backup / point-in-time recovery[c]YesQuery cache supportYesUpdate statistics for data dictionaryYes

[a] Implemented in the server (via encryption functions), rather than in the storage engine.

[b] Implemented in the server, rather than in the storage engine.

[c] Implemented in the server, rather than in the storage engine.


The ARCHIVE storage engine is included in MySQL binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the --with-archive-storage-engine option.

To examine the source for the ARCHIVE engine, look in the storage/archive directory of a MySQL source distribution.

You can check whether the ARCHIVE storage engine is available with the SHOW ENGINES statement.

When you create an ARCHIVE table, the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension. The storage engine creates other files, all having names beginning with the table name. The data file has an extension of .ARZ. (Prior to MySQL 5.1.15, a metadata file with an extension of .ARM is created as well.) An .ARN file may appear during optimization operations.

The ARCHIVE engine supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE. It does support ORDER BY operations, BLOB columns, and basically all but spatial data types (see Section 11.5.1, “Spatial Data Types”). The ARCHIVE engine uses row-level locking.

As of MySQL 5.1.6, the ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error. The ARCHIVE engine also supports the AUTO_INCREMENT table option in CREATE TABLE statements to specify the initial sequence value for a new table or reset the sequence value for an existing table, respectively.

ARCHIVE does not support inserting a value into an AUTO_INCREMENT column less than the current maximum column value. Attempts to do so result in an ER_DUP_KEY error.

As of MySQL 5.1.6, the ARCHIVE engine ignores BLOB columns if they are not requested and scans past them while reading. Formerly, the following two statements had the same cost, but as of 5.1.6, the second is much more efficient than the first:

SELECT a, b, blob_col FROM archive_table;
SELECT a, b FROM archive_table;

Storage: Rows are compressed as they are inserted. The ARCHIVE engine uses zlib lossless data compression (see http://www.zlib.net/). You can use OPTIMIZE TABLE to analyze the table and pack it into a smaller format (for a reason to use OPTIMIZE TABLE, see later in this section). The engine also supports CHECK TABLE. There are several types of insertions that are used:

Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows. SELECT is performed as a consistent read. Note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used. To achieve better compression, you can use OPTIMIZE TABLE or REPAIR TABLE. The number of rows in ARCHIVE tables reported by SHOW TABLE STATUS is always accurate. See Section 13.7.2.5, “OPTIMIZE TABLE Syntax”, Section 13.7.2.6, “REPAIR TABLE Syntax”, and Section 13.7.5.38, “SHOW TABLE STATUS Syntax”.

Warning

Known issue: After a binary upgrade to MySQL 5.1 from a MySQL 5.0 installation that contains ARCHIVE tables:

In either case, the solution is to use mysqldump to dump all 5.0 ARCHIVE tables before upgrading, and reload them into MySQL 5.1 after upgrading. This problem is fixed in MySQL 5.6.4: The server can open ARCHIVE tables created in MySQL 5.0. However, it remains the recommended upgrade procedure to dump 5.0 ARCHIVE tables before upgrading and reload after upgrading.

Additional Resources