<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="memory-storage-engine">
      </a>
      18.3 The MEMORY Storage Engine
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045147958032">
  </a>
  <a class="indexterm" name="idm46045147956992">
  </a>
  <a class="indexterm" name="idm46045147955920">
  </a>
  <a class="indexterm" name="idm46045147954432">
  </a>
  <p>
   The
   <code class="literal">
    MEMORY
   </code>
   storage engine (formerly known as
   <code class="literal">
    HEAP
   </code>
   ) creates special-purpose tables with
    contents that are stored in memory. Because the data is vulnerable
    to crashes, hardware issues, or power outages, only use these tables
    as temporary work areas or read-only caches for data pulled from
    other tables.
  </p>
  <div class="table">
   <a name="idm46045147950816">
   </a>
   <p class="title">
    <b>
     Table 18.4 MEMORY Storage Engine Features
    </b>
   </p>
   <div class="table-contents">
    <table frame="box" rules="all" summary="Features supported by the MEMORY 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>
        Yes
       </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>
        No
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Data caches
         </strong>
        </span>
       </td>
       <td>
        N/A
       </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>
        No
       </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>
        Yes
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Index caches
         </strong>
        </span>
       </td>
       <td>
        N/A
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Locking granularity
         </strong>
        </span>
       </td>
       <td>
        Table
       </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>
        Limited (See the discussion later in this section.)
       </td>
      </tr>
      <tr>
       <td>
        <span class="bold">
         <strong>
          Storage limits
         </strong>
        </span>
       </td>
       <td>
        RAM
       </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"/>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-compared-cluster" title="When to Use MEMORY or NDB Cluster">
       When to Use MEMORY or NDB Cluster
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-partitioning" title="Partitioning">
       Partitioning
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-performance-characteristics" title="Performance Characteristics">
       Performance Characteristics
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-characteristics-of-memory-tables" title="Characteristics of MEMORY Tables">
       Characteristics of MEMORY Tables
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-ddl-operations-for-memory-tables" title="DDL Operations for MEMORY Tables">
       DDL Operations for MEMORY Tables
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-indexes" title="Indexes">
       Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-user-created-and-temporary-tables" title="User-Created and Temporary Tables">
       User-Created and Temporary Tables
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-loading-data" title="Loading Data">
       Loading Data
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-tables-replication" title="MEMORY Tables and Replication">
       MEMORY Tables and Replication
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-managing-memory-use" title="Managing Memory Use">
       Managing Memory Use
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="memory-storage-engine.html#memory-storage-engine-additional-resources" title="Additional Resources">
       Additional Resources
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-compared-cluster">
       </a>
       When to Use MEMORY or NDB Cluster
      </h3>
     </div>
    </div>
   </div>
   <p>
    Developers looking to deploy applications that use the
    <code class="literal">
     MEMORY
    </code>
    storage engine for important, highly
      available, or frequently updated data should consider whether NDB
      Cluster is a better choice. A typical use case for the
    <code class="literal">
     MEMORY
    </code>
    engine involves these characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Operations involving transient, non-critical data such as
          session management or caching. When the MySQL server halts or
          restarts, the data in
       <code class="literal">
        MEMORY
       </code>
       tables is
          lost.
      </p>
     </li>
     <li class="listitem">
      <p>
       In-memory storage for fast access and low latency. Data volume
          can fit entirely in memory without causing the operating
          system to swap out virtual memory pages.
      </p>
     </li>
     <li class="listitem">
      <p>
       A read-only or read-mostly data access pattern (limited
          updates).
      </p>
     </li>
    </ul>
   </div>
   <p>
    NDB Cluster offers the same features as the
    <code class="literal">
     MEMORY
    </code>
    engine with higher performance levels,
      and provides additional features not available with
    <code class="literal">
     MEMORY
    </code>
    :
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Row-level locking and multiple-thread operation for low
          contention between clients.
      </p>
     </li>
     <li class="listitem">
      <p>
       Scalability even with statement mixes that include writes.
      </p>
     </li>
     <li class="listitem">
      <p>
       Optional disk-backed operation for data durability.
      </p>
     </li>
     <li class="listitem">
      <p>
       Shared-nothing architecture and multiple-host operation with
          no single point of failure, enabling 99.999% availability.
      </p>
     </li>
     <li class="listitem">
      <p>
       Automatic data distribution across nodes; application
          developers need not craft custom sharding or partitioning
          solutions.
      </p>
     </li>
     <li class="listitem">
      <p>
       Support for variable-length data types (including
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         BLOB
        </code>
       </a>
       and
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         TEXT
        </code>
       </a>
       ) not supported by
       <code class="literal">
        MEMORY
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-partitioning">
       </a>
       Partitioning
      </h3>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     MEMORY
    </code>
    tables cannot be partitioned.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-performance-characteristics">
       </a>
       Performance Characteristics
      </h3>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     MEMORY
    </code>
    performance is constrained by contention
      resulting from single-thread execution and table lock overhead
      when processing updates. This limits scalability when load
      increases, particularly for statement mixes that include writes.
   </p>
   <p>
    Despite the in-memory processing for
    <code class="literal">
     MEMORY
    </code>
    tables, they are not necessarily faster than
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    tables on a busy server, for
      general-purpose queries, or under a read/write workload. In
      particular, the table locking involved with performing updates can
      slow down concurrent usage of
    <code class="literal">
     MEMORY
    </code>
    tables
      from multiple sessions.
   </p>
   <p>
    Depending on the kinds of queries performed on a
    <code class="literal">
     MEMORY
    </code>
    table, you might create indexes as
      either the default hash data structure (for looking up single
      values based on a unique key), or a general-purpose B-tree data
      structure (for all kinds of queries involving equality,
      inequality, or range operators such as less than or greater than).
      The following sections illustrate the syntax for creating both
      kinds of indexes. A common performance issue is using the default
      hash indexes in workloads where B-tree indexes are more efficient.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-characteristics-of-memory-tables">
       </a>
       Characteristics of MEMORY Tables
      </h3>
     </div>
    </div>
   </div>
   <p>
    The
    <code class="literal">
     MEMORY
    </code>
    storage engine does not create any
      files on disk. The table definition is stored in the MySQL data
      dictionary.
   </p>
   <p>
    <code class="literal">
     MEMORY
    </code>
    tables have the following
      characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Space for
       <code class="literal">
        MEMORY
       </code>
       tables is allocated in
          small blocks. Tables use 100% dynamic hashing for inserts. No
          overflow area or extra key space is needed. No extra space is
          needed for free lists. Deleted rows are put in a linked list
          and are reused when you insert new data into the table.
       <code class="literal">
        MEMORY
       </code>
       tables also have none of the
          problems commonly associated with deletes plus inserts in
          hashed tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        MEMORY
       </code>
       tables use a fixed-length
          row-storage format. Variable-length types such as
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       are stored using a
          fixed length.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        MEMORY
       </code>
       tables cannot contain
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         BLOB
        </code>
       </a>
       or
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         TEXT
        </code>
       </a>
       columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        MEMORY
       </code>
       includes support for
       <code class="literal">
        AUTO_INCREMENT
       </code>
       columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       Non-
       <code class="literal">
        TEMPORARY
       </code>
       <code class="literal">
        MEMORY
       </code>
       tables are shared among all clients, just like any other
          non-
       <code class="literal">
        TEMPORARY
       </code>
       table.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-ddl-operations-for-memory-tables">
       </a>
       DDL Operations for MEMORY Tables
      </h3>
     </div>
    </div>
   </div>
   <p>
    To create a
    <code class="literal">
     MEMORY
    </code>
    table, specify the clause
    <code class="literal">
     ENGINE=MEMORY
    </code>
    on the
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    statement.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa33889725"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">MEMORY</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    As indicated by the engine name,
    <code class="literal">
     MEMORY
    </code>
    tables
      are stored in memory. They use hash indexes by default, which
      makes them very fast for single-value lookups, and very useful for
      creating temporary tables. However, when the server shuts down,
      all rows stored in
    <code class="literal">
     MEMORY
    </code>
    tables are lost. The
      tables themselves continue to exist because their definitions are
      stored in the MySQL data dictionary, but they are empty when the
      server restarts.
   </p>
   <p>
    This example shows how you might create, use, and remove a
    <code class="literal">
     MEMORY
    </code>
    table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa98454567"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">MEMORY</span>
           <span class="token keyword">SELECT</span> ip<span class="token punctuation">,</span><span class="token function">SUM</span><span class="token punctuation">(</span>downloads<span class="token punctuation">)</span> <span class="token keyword">AS</span> down
           <span class="token keyword">FROM</span> log_table <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> ip<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span>ip<span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token function">AVG</span><span class="token punctuation">(</span>down<span class="token punctuation">)</span> <span class="token keyword">FROM</span> test<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> test<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The maximum size of
    <code class="literal">
     MEMORY
    </code>
    tables is limited by
      the
    <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
     <code class="literal">
      max_heap_table_size
     </code>
    </a>
    system
      variable, which has a default value of 16MB. To enforce different
      size limits for
    <code class="literal">
     MEMORY
    </code>
    tables, change the value
      of this variable. The value in effect for
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    , or a subsequent
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    or
    <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
     <code class="literal">
      TRUNCATE TABLE
     </code>
    </a>
    , is the value used
      for the life of the table. A server restart also sets the maximum
      size of existing
    <code class="literal">
     MEMORY
    </code>
    tables to the global
    <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
     <code class="literal">
      max_heap_table_size
     </code>
    </a>
    value. You
      can set the size for individual tables as described later in this
      section.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-indexes">
       </a>
       Indexes
      </h3>
     </div>
    </div>
   </div>
   <p>
    The
    <code class="literal">
     MEMORY
    </code>
    storage engine supports both
    <code class="literal">
     HASH
    </code>
    and
    <code class="literal">
     BTREE
    </code>
    indexes. You
      can specify one or the other for a given index by adding a
    <code class="literal">
     USING
    </code>
    clause as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13708515"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> lookup
    <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span> <span class="token keyword">USING</span> <span class="token keyword">HASH</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">)</span>
    <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">MEMORY</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> lookup
    <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span> <span class="token keyword">USING</span> <span class="token keyword">BTREE</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">)</span>
    <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">MEMORY</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For general characteristics of B-tree and hash indexes, see
    <a class="xref" href="mysql-indexes.html" title="10.3.1 How MySQL Uses Indexes">
     Section 10.3.1, “How MySQL Uses Indexes”
    </a>
    .
   </p>
   <p>
    <code class="literal">
     MEMORY
    </code>
    tables can have up to 64 indexes per
      table, 16 columns per index and a maximum key length of 3072
      bytes.
   </p>
   <p>
    If a
    <code class="literal">
     MEMORY
    </code>
    table hash index has a high degree
      of key duplication (many index entries containing the same value),
      updates to the table that affect key values and all deletes are
      significantly slower. The degree of this slowdown is proportional
      to the degree of duplication (or, inversely proportional to the
      index cardinality). You can use a
    <code class="literal">
     BTREE
    </code>
    index
      to avoid this problem.
   </p>
   <p>
    <code class="literal">
     MEMORY
    </code>
    tables can have nonunique keys. (This is
      an uncommon feature for implementations of hash indexes.)
   </p>
   <p>
    Columns that are indexed can contain
    <code class="literal">
     NULL
    </code>
    values.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-user-created-and-temporary-tables">
       </a>
       User-Created and Temporary Tables
      </h3>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     MEMORY
    </code>
    table contents are stored in memory,
      which is a property that
    <code class="literal">
     MEMORY
    </code>
    tables share
      with internal temporary tables that the server creates on the fly
      while processing queries. However, the two types of tables differ
      in that
    <code class="literal">
     MEMORY
    </code>
    tables are not subject to
      storage conversion, whereas internal temporary tables are:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If an internal temporary table becomes too large, the server
          automatically converts it to on-disk storage, as described in
       <a class="xref" href="internal-temporary-tables.html" title="10.4.4 Internal Temporary Table Use in MySQL">
        Section 10.4.4, “Internal Temporary Table Use in MySQL”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       User-created
       <code class="literal">
        MEMORY
       </code>
       tables are never
          converted to disk tables.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-loading-data">
       </a>
       Loading Data
      </h3>
     </div>
    </div>
   </div>
   <p>
    To populate a
    <code class="literal">
     MEMORY
    </code>
    table when the MySQL
      server starts, you can use the
    <a class="link" href="server-system-variables.html#sysvar_init_file">
     <code class="literal">
      init_file
     </code>
    </a>
    system variable. For
      example, you can put statements such as
    <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
     <code class="literal">
      INSERT INTO ...
      SELECT
     </code>
    </a>
    or
    <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
     <code class="literal">
      LOAD DATA
     </code>
    </a>
    into
      a file to load the table from a persistent data source, and use
    <a class="link" href="server-system-variables.html#sysvar_init_file">
     <code class="literal">
      init_file
     </code>
    </a>
    to name the file. See
    <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
     Section 7.1.8, “Server System Variables”
    </a>
    , and
    <a class="xref" href="load-data.html" title="15.2.9 LOAD DATA Statement">
     Section 15.2.9, “LOAD DATA Statement”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-tables-replication">
       </a>
       MEMORY Tables and Replication
      </h3>
     </div>
    </div>
   </div>
   <p>
    When a replication source server shuts down and restarts, its
    <a class="link" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
     <code class="literal">
      MEMORY
     </code>
    </a>
    tables become empty. To
      replicate this effect to replicas, the first time that the source
      uses a given
    <a class="link" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
     <code class="literal">
      MEMORY
     </code>
    </a>
    table after
      startup, it logs an event that notifies replicas that the table
      must be emptied by writing a
    <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
     <code class="literal">
      TRUNCATE
      TABLE
     </code>
    </a>
    statement for that table to the binary log. When a
      replica server shuts down and restarts, its
    <a class="link" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
     <code class="literal">
      MEMORY
     </code>
    </a>
    tables also become empty, and
      it writes a
    <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
     <code class="literal">
      TRUNCATE TABLE
     </code>
    </a>
    statement to its own binary log, which is passed on to any
      downstream replicas.
   </p>
   <p>
    When you use
    <a class="link" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
     <code class="literal">
      MEMORY
     </code>
    </a>
    tables in a
      replication topology, in some situations, the table on the source
      and the table on the replica can differ. For information on
      handling each of these situations to prevent stale reads or
      errors, see
    <a class="xref" href="replication-features-memory.html" title="19.5.1.21 Replication and MEMORY Tables">
     Section 19.5.1.21, “Replication and MEMORY Tables”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-managing-memory-use">
       </a>
       Managing Memory Use
      </h3>
     </div>
    </div>
   </div>
   <p>
    The server needs sufficient memory to maintain all
    <code class="literal">
     MEMORY
    </code>
    tables that are in use at the same time.
   </p>
   <p>
    Memory is not reclaimed if you delete individual rows from a
    <code class="literal">
     MEMORY
    </code>
    table. Memory is reclaimed only when the
      entire table is deleted. Memory that was previously used for
      deleted rows is re-used for new rows within the same table. To
      free all the memory used by a
    <code class="literal">
     MEMORY
    </code>
    table when
      you no longer require its contents, execute
    <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
     <code class="literal">
      DELETE
     </code>
    </a>
    or
    <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
     <code class="literal">
      TRUNCATE TABLE
     </code>
    </a>
    to remove all rows,
      or remove the table altogether using
    <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
     <code class="literal">
      DROP
      TABLE
     </code>
    </a>
    . To free up the memory used by deleted rows, use
    <code class="literal">
     ALTER TABLE ENGINE=MEMORY
    </code>
    to force a table
      rebuild.
   </p>
   <p>
    The memory needed for one row in a
    <code class="literal">
     MEMORY
    </code>
    table
      is calculated using the following expression:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61101822"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql">SUM_OVER_ALL_BTREE_KEYS<span class="token punctuation">(</span><em class="replaceable">max_length_of_key</em> <span class="token operator">+</span> sizeof<span class="token punctuation">(</span><span class="token datatype">char</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">4</span><span class="token punctuation">)</span>
<span class="token operator">+</span> SUM_OVER_ALL_HASH_KEYS<span class="token punctuation">(</span>sizeof<span class="token punctuation">(</span><span class="token datatype">char</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token operator">*</span> <span class="token number">2</span><span class="token punctuation">)</span>
<span class="token operator">+</span> ALIGN<span class="token punctuation">(</span><em class="replaceable">length_of_row</em><span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">,</span> sizeof<span class="token punctuation">(</span><span class="token datatype">char</span><span class="token operator">*</span><span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    <code class="literal">
     ALIGN()
    </code>
    represents a round-up factor to cause
      the row length to be an exact multiple of the
    <code class="literal">
     char
    </code>
    pointer size.
    <code class="literal">
     sizeof(char*)
    </code>
    is 4 on 32-bit machines and 8 on
      64-bit machines.
   </p>
   <p>
    As mentioned earlier, the
    <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
     <code class="literal">
      max_heap_table_size
     </code>
    </a>
    system
      variable sets the limit on the maximum size of
    <code class="literal">
     MEMORY
    </code>
    tables. To control the maximum size for
      individual tables, set the session value of this variable before
      creating each table. (Do not change the global
    <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
     <code class="literal">
      max_heap_table_size
     </code>
    </a>
    value unless
      you intend the value to be used for
    <code class="literal">
     MEMORY
    </code>
    tables created by all clients.) The following example creates two
    <code class="literal">
     MEMORY
    </code>
    tables, with a maximum size of 1MB and
      2MB, respectively:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75659818"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> max_heap_table_size <span class="token operator">=</span> <span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">UNIQUE</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">MEMORY</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> max_heap_table_size <span class="token operator">=</span> <span class="token number">1024</span><span class="token operator">*</span><span class="token number">1024</span><span class="token operator">*</span><span class="token number">2</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">UNIQUE</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">MEMORY</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span></code></pre>
   </div>
   <p>
    Both tables revert to the server's global
    <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
     <code class="literal">
      max_heap_table_size
     </code>
    </a>
    value if the
      server restarts.
   </p>
   <p>
    You can also specify a
    <code class="literal">
     MAX_ROWS
    </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 for
    <code class="literal">
     MEMORY
    </code>
    tables to provide a hint about the
      number of rows you plan to store in them. This does not enable the
      table to grow beyond the
    <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
     <code class="literal">
      max_heap_table_size
     </code>
    </a>
    value, which
      still acts as a constraint on maximum table size. For maximum
      flexibility in being able to use
    <code class="literal">
     MAX_ROWS
    </code>
    , set
    <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
     <code class="literal">
      max_heap_table_size
     </code>
    </a>
    at least as
      high as the value to which you want each
    <code class="literal">
     MEMORY
    </code>
    table to be able to grow.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="memory-storage-engine-additional-resources">
       </a>
       Additional Resources
      </h3>
     </div>
    </div>
   </div>
   <p>
    A forum dedicated to the
    <code class="literal">
     MEMORY
    </code>
    storage engine
      is available at
    <a class="ulink" href="https://forums.mysql.com/list.php?92" target="_blank">
     https://forums.mysql.com/list.php?92
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
