14.8. The ARCHIVE Storage Engine
The ARCHIVE storage engine is used for storing
large amounts of data without indexes in a very small footprint.
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 this statement:
mysql> SHOW VARIABLES LIKE 'have_archive';
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 and metadata files
have extensions of .ARZ and
.ARM, respectively. 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 18.4.1, “MySQL 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 columns can have either a unique
or non-unique 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 and ALTER
TABLE statements to specify the initial sequence value
for a new table or reset the sequence value for an existing table,
respectively.
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
https://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:
An 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
SELECT forces a flush to occur, unless the
only insertions that have come in were INSERT
DELAYED (those flush as necessary). See
Section 13.2.4.2, “INSERT DELAYED Syntax”.
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 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
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.5.2.5, “OPTIMIZE TABLE Syntax”,
Section 13.5.2.6, “REPAIR TABLE Syntax”, and
Section 13.5.4.24, “SHOW TABLE STATUS Syntax”.
Additional resources
