ARCHIVE storage engine is used for storing
large amounts of data without indexes in a very small footprint.
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
sql directory of a MySQL source
You can check whether the
ARCHIVE storage engine
is available with this statement:
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
extension. The storage engine creates other files, all having names
beginning with the table name. The data and metadata files have
.ARM, respectively. An
.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.
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). Beginning with MySQL 5.0.15, the engine also supports
CHECK TABLE. There are several types
of insertions that are used:
INSERTstatement just pushes rows into a compression buffer, and that buffer flushes as necessary. The insertion into the buffer is protected by a lock. A
SELECTforces a flush to occur, unless the only insertions that have come in were
INSERT DELAYED(those flush as necessary). See Section 184.108.40.206, “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
SELECTnever 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 220.127.116.11, “OPTIMIZE TABLE Syntax”,
Section 18.104.22.168, “REPAIR TABLE Syntax”, and
Section 22.214.171.124, “SHOW TABLE STATUS Syntax”.
A forum dedicated to the
ARCHIVEstorage engine is available at http://forums.mysql.com/list.php?112.