The ARCHIVE
storage engine was added in MySQL
4.1.3. It 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 sql
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 16.4.1, “MySQL 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). 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 12.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 12.4.2.5, “OPTIMIZE TABLE
Syntax”,
Section 12.4.2.6, “REPAIR TABLE
Syntax”, and
Section 12.4.5.23, “SHOW TABLE STATUS
Syntax”.
Additional Resources
A forum dedicated to the ARCHIVE
storage
engine is available at
http://forums.mysql.com/list.php?112.
User Comments
The ARCHIVE type does not support AUTO_INCREMENT columns (MySQL 5.0.45)
Add your own comment.