ARCHIVE storage engine is used for storing
large amounts of data without indexes in a very small footprint.
ARCHIVE Storage Engine
|Storage limits||None||Transactions||No||Locking granularity||Table|
|MVCC||No||Geospatial data type support||Yes||Geospatial indexing support||No|
|B-tree indexes||No||T-tree indexes||No||Hash indexes||No|
|Full-text search indexes||No||Clustered indexes||No||Data caches||No|
|Index caches||No||Compressed data||Yes||Encrypted data[a]||Yes|
|Cluster database support||No||Replication support[b]||Yes||Foreign key support||No|
|Backup / point-in-time recovery[c]||Yes||Query cache support||Yes||Update statistics for data dictionary||Yes|
[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.
ARCHIVE storage engine is included in MySQL
binary distributions. To enable this storage engine if you build
MySQL from source, invoke configure with the
To examine the source for the
look in the
storage/archive directory of a
MySQL source distribution.
You can check whether the
ARCHIVE storage engine
is available with the
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
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.)
.ARN file may appear during optimization
ARCHIVE engine supports
SELECT, but not
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
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
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
AUTO_INCREMENT column less than the current
maximum column value. Attempts to do so result in an
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
TABLE. There are several types of insertions that are
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.
SELECT forces a flush to occur,
unless the only insertions that have come in were
INSERT DELAYED (those flush as
necessary). See Section 22.214.171.124, “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
SELECT never causes
a flush of a bulk insert unless a normal insert occurs while it
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 126.96.36.199, “OPTIMIZE TABLE Syntax”,
Section 188.8.131.52, “REPAIR TABLE Syntax”, and
Section 184.108.40.206, “SHOW TABLE STATUS Syntax”.
Known issue: After a binary
upgrade to MySQL 5.1 from a MySQL 5.0 installation that contains
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.
A forum dedicated to the
engine is available at http://forums.mysql.com/list.php?112.