ARCHIVE storage engine produces special-purpose tables that store large amounts
of unindexed data in a very small footprint.
ARCHIVE Storage EngineFeatures
|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 CMake
To examine the source for the
ARCHIVE engine, look in the
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
The storage engine creates other files, all having names beginning with the table name. The data file has an
.ARN file may appear during
ARCHIVE engine supports
SELECT, but not
UPDATE. It does support
ORDER BY operations,
columns, and basically all but spatial data types (see Section
184.108.40.206, "MySQL Spatial Data Types"). The
ARCHIVE engine uses row-level
ARCHIVE engine supports 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
engine also supports the
AUTO_INCREMENT table option in
CREATE TABLE and
TABLE statements to specify the initial sequence value for a new table or reset the sequence
value for an existing table, respectively.
ARCHIVE engine ignores
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
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 used:
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
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
tables reported by
SHOW TABLE STATUS
is always accurate. See Section 220.127.116.11, "
TABLE Syntax", Section 18.104.22.168, "
TABLE Syntax", and Section 22.214.171.124, "
SHOW TABLE STATUS Syntax".
A forum dedicated to the
ARCHIVE storage engine is