Source code editor What Is Ajax
↑
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 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). Beginning with MySQL 5.0.15, 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
A forum dedicated to the ARCHIVE
storage engine is available at http://forums.mysql.com/list.php?112.