Pantek Library
Hosting Provided By
CybrHost
High Speed Hosting
Applications: MySQL > doc > refman > 4.1 > enarchive-storage-engine.html (Request Expert MySQL Support)
Skip navigation links

Additional languages
    MySQL 3.23, 4.0, 4.1 Reference Manual :: 13 Storage Engines :: 13.7 The ARCHIVE Storage Engine

    13.7. The ARCHIVE Storage Engine

    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.

    Do you need help?X

    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.5.2.5, “OPTIMIZE TABLE Syntax”, Section 12.5.2.6, “REPAIR TABLE Syntax”, and Section 12.5.4.18, “SHOW TABLE STATUS Syntax”.

    Additional resources


    User Comments

    Posted by Peter Sankauskas on February 7 2008 11:26pm[Delete] [Edit]

    The ARCHIVE type does not support AUTO_INCREMENT columns (MySQL 5.0.45)

    Do you need more help?X

    Add your own comment.
    Contact Us  Legal Notices  Order Services Online 
    Pantek Home  Privacy Policy  IT news  Site Map  Pantek Library