<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="archive-storage-engine">
      </a>
      18.5 The ARCHIVE Storage Engine
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045147691472">
  </a>
  <a class="indexterm" name="idm46045147690432">
  </a>
  <p>
   The
   <code class="literal">
    ARCHIVE
   </code>
   storage engine produces
    special-purpose tables that store large amounts of unindexed data in
    a very small footprint.
  </p>
  <div class="table">
   <a name="idm46045147687648">
   </a>
   <p class="title">
    <b>
     Table 18.5 ARCHIVE Storage Engine Features
    </b>
   </p>
   <div class="table-contents">
    <table frame="box" rules="all" summary="Features supported by the ARCHIVE storage engine.">
     <colgroup>
      <col style="width: 60%"/>
      <col style="width: 40%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Feature
       </th>
       <th>
        Support
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        <span class="bold">
         <strong>
          B-tree indexes
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Backup/point-in-time recovery
         </strong>
        </span>
        (Implemented in the server, rather than in the storage engine.)
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Cluster database support
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Clustered indexes
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Compressed data
         </strong>
        </span>
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Data caches
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Encrypted data
         </strong>
        </span>
       </td>
       <td>
        Yes (Implemented in the server via encryption functions.)
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Foreign key support
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Full-text search indexes
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Geospatial data type support
         </strong>
        </span>
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Geospatial indexing support
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Hash indexes
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Index caches
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Locking granularity
         </strong>
        </span>
       </td>
       <td>
        Row
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          MVCC
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Replication support
         </strong>
        </span>
        (Implemented in the server, rather than in the storage engine.)
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Storage limits
         </strong>
        </span>
       </td>
       <td>
        None
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          T-tree indexes
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Transactions
         </strong>
        </span>
       </td>
       <td>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Update statistics for data dictionary
         </strong>
        </span>
       </td>
       <td>
        Yes
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <div class="table-contents">
    <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 739px;">
     <thead>
      <tr>
       <th style="width: 442.797px;">
        Feature
       </th>
       <th style="width: 295.203px;">
        Support
       </th>
      </tr>
     </thead>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   The
   <code class="literal">
    ARCHIVE
   </code>
   storage engine is included in MySQL
    binary distributions. To enable this storage engine if you build
    MySQL from source, invoke
   <span class="command">
    <strong>
     CMake
    </strong>
   </span>
   with the
   <a class="link" href="source-configuration-options.html#option_cmake_storage_engine_options" title="Storage Engine Options">
    <code class="option">
     -DWITH_ARCHIVE_STORAGE_ENGINE
    </code>
   </a>
   option.
  </p>
  <p>
   To examine the source for the
   <code class="literal">
    ARCHIVE
   </code>
   engine,
    look in the
   <code class="filename">
    storage/archive
   </code>
   directory of a
    MySQL source distribution.
  </p>
  <p>
   You can check whether the
   <code class="literal">
    ARCHIVE
   </code>
   storage engine
    is available with the
   <a class="link" href="show-engines.html" title="15.7.7.17 SHOW ENGINES Statement">
    <code class="literal">
     SHOW ENGINES
    </code>
   </a>
   statement.
  </p>
  <p>
   When you create an
   <code class="literal">
    ARCHIVE
   </code>
   table, the storage
    engine creates files with names that begin with the table name. The
    data file has an extension of
   <code class="filename">
    .ARZ
   </code>
   . An
   <code class="filename">
    .ARN
   </code>
   file may appear during optimization
    operations.
  </p>
  <p>
   The
   <code class="literal">
    ARCHIVE
   </code>
   engine supports
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   ,
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   , and
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   , but not
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   or
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   . It does support
   <code class="literal">
    ORDER BY
   </code>
   operations,
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   columns, and spatial data types
    (see
   <a class="xref" href="spatial-type-overview.html" title="13.4.1 Spatial Data Types">
    Section 13.4.1, “Spatial Data Types”
   </a>
   ). Geographic spatial
    reference systems are not supported. The
   <code class="literal">
    ARCHIVE
   </code>
   engine uses row-level locking.
  </p>
  <p>
   The
   <code class="literal">
    ARCHIVE
   </code>
   engine supports the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column attribute. The
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column can have either a unique or
    nonunique index. Attempting to create an index on any other column
    results in an error. The
   <code class="literal">
    ARCHIVE
   </code>
   engine also
    supports the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   table option in
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statements to specify
    the initial sequence value for a new table or reset the sequence
    value for an existing table, respectively.
  </p>
  <p>
   <code class="literal">
    ARCHIVE
   </code>
   does not support inserting a value into
    an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column less than the current
    maximum column value. Attempts to do so result in an
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_dup_key" target="_top">
    <code class="literal">
     ER_DUP_KEY
    </code>
   </a>
   error.
  </p>
  <p>
   The
   <code class="literal">
    ARCHIVE
   </code>
   engine ignores
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   columns if they are not
    requested and scans past them while reading.
  </p>
  <p>
   The
   <code class="literal">
    ARCHIVE
   </code>
   storage engine does not support
    partitioning.
  </p>
  <p>
   <span class="bold">
    <strong>
     Storage:
    </strong>
   </span>
   Rows are compressed as
    they are inserted. The
   <code class="literal">
    ARCHIVE
   </code>
   engine uses
   <code class="literal">
    zlib
   </code>
   lossless data compression (see
   <a class="ulink" href="http://www.zlib.net/" target="_blank">
    http://www.zlib.net/
   </a>
   ). You can use
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   to analyze the table
    and pack it into a smaller format (for a reason to use
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   , see later in this
    section). The engine also supports
   <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
    <code class="literal">
     CHECK
    TABLE
    </code>
   </a>
   . There are several types of insertions that are
    used:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      An
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      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
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      forces a flush to occur.
     </p>
    </li>
    <li class="listitem">
     <p>
      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
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      never causes
        a flush of a bulk insert unless a normal insert occurs while it
        is loading.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <span class="bold">
    <strong>
     Retrieval
    </strong>
   </span>
   : On retrieval, rows are
    uncompressed on demand; there is no row cache. A
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   operation performs a complete
    table scan: When a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   occurs, it
    finds out how many rows are currently available and reads that
    number of rows.
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   is performed
    as a consistent read. Note that lots of
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements during insertion
    can deteriorate the compression, unless only bulk inserts are used.
    To achieve better compression, you can use
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   or
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR TABLE
    </code>
   </a>
   . The number of rows in
   <code class="literal">
    ARCHIVE
   </code>
   tables reported by
   <a class="link" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
    <code class="literal">
     SHOW TABLE STATUS
    </code>
   </a>
   is always accurate.
    See
   <a class="xref" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    Section 15.7.3.4, “OPTIMIZE TABLE Statement”
   </a>
   ,
   <a class="xref" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    Section 15.7.3.5, “REPAIR TABLE Statement”
   </a>
   , and
   <a class="xref" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
    Section 15.7.7.38, “SHOW TABLE STATUS Statement”
   </a>
   .
  </p>
  <h3>
   <a name="idm46045147573680">
   </a>
   Additional Resources
  </h3>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A forum dedicated to the
      <code class="literal">
       ARCHIVE
      </code>
      storage
        engine is available at
      <a class="ulink" href="https://forums.mysql.com/list.php?112" target="_blank">
       https://forums.mysql.com/list.php?112
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
