The 'ARCHIVE' storage engine produces special-purpose tables that store large amounts of unindexed data in a very small footprint.
ARCHIVE Storage Engine Features
Feature Support
B-tree indexes No
Backup/point-in-time recovery Yes (Implemented in the server, rather than
in the storage engine.)
Cluster database support No
Clustered indexes No
Compressed data Yes
Data caches No
Encrypted data Yes (Implemented in the server via encryption functions.)
Foreign key support No
Full-text search indexes No
Geospatial data type support Yes
Geospatial indexing support No
Hash indexes No
Index caches No
Locking granularity Row
MVCC No
Replication support (Implemented in the Yes server, rather than in the storage
engine.)
Storage limits None
T-tree indexes No
Transactions No
Update statistics for data dictionary Yes
The 'ARCHIVE' storage engine is included in MySQL binary distributions. To enable this storage engine if you build MySQL from source, invoke 'CMake' with the '-DWITH_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 *note 'SHOW ENGINES': 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'. An '.ARN' file may appear during optimization operations.
The 'ARCHIVE' engine supports note 'INSERT': insert, note 'REPLACE': replace, and note 'SELECT': select, but not note 'DELETE': delete. or note 'UPDATE': update. It does support 'ORDER BY' operations, note 'BLOB': blob. columns, and basically all data types including spatial data types (see *note spatial-type-overview::). Geographic spatial reference systems are not supported. The 'ARCHIVE' engine uses row-level locking.
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 *note 'CREATE TABLE': 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' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_dup_key) error.
The 'ARCHIVE' engine ignores *note 'BLOB': blob. columns if they are not requested and scans past them while reading.
Storage: Rows are compressed as they are inserted. The 'ARCHIVE' engine uses 'zlib' lossless data compression (see http://www.zlib.net/). You can use note 'OPTIMIZE TABLE': optimize-table. to analyze the table and pack it into a smaller format (for a reason to use note 'OPTIMIZE TABLE': optimize-table, see later in this section). The engine also supports *note 'CHECK TABLE': check-table. There are several types of insertions that are used:
An note 'INSERT': insert. statement just pushes rows into a compression buffer, and that buffer flushes as necessary. The insertion into the buffer is protected by a lock. A note 'SELECT': select. forces a flush to occur.
A bulk insert is visible only after it completes, unless other inserts occur at the same time, in which case it can be seen partially. A *note 'SELECT': select. never causes a flush of a bulk insert unless a normal insert occurs while it is loading.
Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A note 'SELECT': select. operation performs a complete table scan: When a note 'SELECT': select. occurs, it finds out how many rows are currently available and reads that number of rows. note 'SELECT': select. is performed as a consistent read. Note that lots of note 'SELECT': select. statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used. To achieve better compression, you can use note 'OPTIMIZE TABLE': optimize-table. or note 'REPAIR TABLE': repair-table. The number of rows in 'ARCHIVE' tables reported by note 'SHOW TABLE STATUS': show-table-status. is always accurate. See note optimize-table::, note repair-table::, and note show-table-status::.
Additional Resources
File: manual.info.tmp, Node: blackhole-storage-engine, Next: merge-storage-engine, Prev: archive-storage-engine, Up: storage-engines