<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="cache-index">
      </a>
      15.7.8.2 CACHE INDEX Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045168851088">
  </a>
  <a class="indexterm" name="idm46045168850016">
  </a>
  <a class="indexterm" name="idm46045168848528">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73783906"><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">CACHE</span> <span class="token keyword">INDEX</span> {
      <em class="replaceable">tbl_index_list</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">tbl_index_list</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
    <span class="token operator">|</span> <em class="replaceable">tbl_name</em> <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_list</em><span class="token punctuation">)</span>
  }
  <span class="token keyword">IN</span> <em class="replaceable">key_cache_name</em>

<em class="replaceable">tbl_index_list</em>:
  <em class="replaceable">tbl_name</em> <span class="token punctuation">[</span>{<span class="token keyword">INDEX</span><span class="token operator">|</span><span class="token keyword">KEY</span>} <span class="token punctuation">(</span><em class="replaceable">index_name</em><span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">]</span>

<em class="replaceable">partition_list</em>: {
    <em class="replaceable">partition_name</em><span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">partition_name</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token operator">|</span> <span class="token keyword">ALL</span>
}</code></pre>
  </div>
  <p>
   The
   <a class="link" href="cache-index.html" title="15.7.8.2 CACHE INDEX Statement">
    <code class="literal">
     CACHE INDEX
    </code>
   </a>
   statement assigns
        table indexes to a specific key cache. It applies only to
   <code class="literal">
    MyISAM
   </code>
   tables, including partitioned
   <code class="literal">
    MyISAM
   </code>
   tables. After the indexes have been
        assigned, they can be preloaded into the cache if desired with
   <a class="link" href="load-index.html" title="15.7.8.5 LOAD INDEX INTO CACHE Statement">
    <code class="literal">
     LOAD INDEX INTO
        CACHE
    </code>
   </a>
   .
  </p>
  <p>
   The following statement assigns indexes from the tables
   <code class="literal">
    t1
   </code>
   ,
   <code class="literal">
    t2
   </code>
   , and
   <code class="literal">
    t3
   </code>
   to the key cache named
   <code class="literal">
    hot_cache
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74233844"><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">CACHE</span> <span class="token keyword">INDEX</span> t1<span class="token punctuation">,</span> t2<span class="token punctuation">,</span> t3 <span class="token keyword">IN</span> hot_cache<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Table   <span class="token punctuation">|</span> Op                 <span class="token punctuation">|</span> Msg_type <span class="token punctuation">|</span> Msg_text <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> test.t1 <span class="token punctuation">|</span> assign_to_keycache <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> OK       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> test.t2 <span class="token punctuation">|</span> assign_to_keycache <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> OK       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> test.t3 <span class="token punctuation">|</span> assign_to_keycache <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> OK       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The syntax of
   <a class="link" href="cache-index.html" title="15.7.8.2 CACHE INDEX Statement">
    <code class="literal">
     CACHE INDEX
    </code>
   </a>
   enables
        you to specify that only particular indexes from a table should
        be assigned to the cache. However, the implementation assigns
        all the table's indexes to the cache, so there is no reason to
        specify anything other than the table name.
  </p>
  <p>
   The key cache referred to in a
   <a class="link" href="cache-index.html" title="15.7.8.2 CACHE INDEX Statement">
    <code class="literal">
     CACHE
        INDEX
    </code>
   </a>
   statement can be created by setting its size
        with a parameter setting statement or in the server parameter
        settings. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa13154485"><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">SET</span> <span class="token keyword">GLOBAL</span> keycache1<span class="token punctuation">.</span>key_buffer_size<span class="token operator">=</span><span class="token number">128</span><span class="token operator">*</span><span class="token number">1024</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Key cache parameters are accessed as members of a structured
        system variable. See
   <a class="xref" href="structured-system-variables.html" title="7.1.9.5 Structured System Variables">
    Section 7.1.9.5, “Structured System Variables”
   </a>
   .
  </p>
  <p>
   A key cache must exist before you assign indexes to it, or an
        error occurs:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13413367"><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">CACHE</span> <span class="token keyword">INDEX</span> t1 <span class="token keyword">IN</span> non_existent_cache<span class="token punctuation">;</span>
<span class="token output">ERROR 1284 (HY000)<span class="token punctuation">:</span> Unknown key cache 'non_existent_cache'</span></code></pre>
  </div>
  <p>
   By default, table indexes are assigned to the main (default) key
        cache created at the server startup. When a key cache is
        destroyed, all indexes assigned to it are reassigned to the
        default key cache.
  </p>
  <p>
   Index assignment affects the server globally: If one client
        assigns an index to a given cache, this cache is used for all
        queries involving the index, no matter which client issues the
        queries.
  </p>
  <p>
   <a class="link" href="cache-index.html" title="15.7.8.2 CACHE INDEX Statement">
    <code class="literal">
     CACHE INDEX
    </code>
   </a>
   is supported for
        partitioned
   <code class="literal">
    MyISAM
   </code>
   tables. You can assign one
        or more indexes for one, several, or all partitions to a given
        key cache. For example, you can do the following:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29898111"><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> pt <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span> i<span class="token punctuation">(</span>c1<span class="token punctuation">)</span><span class="token punctuation">)</span>
    <span class="token keyword">ENGINE</span><span class="token operator">=</span>MyISAM
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span>c1<span class="token punctuation">)</span>
    <span class="token keyword">PARTITIONS</span> <span class="token number">4</span><span class="token punctuation">;</span>

<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> kc_fast<span class="token punctuation">.</span>key_buffer_size <span class="token operator">=</span> <span class="token number">128</span> <span class="token operator">*</span> <span class="token number">1024</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> kc_slow<span class="token punctuation">.</span>key_buffer_size <span class="token operator">=</span> <span class="token number">128</span> <span class="token operator">*</span> <span class="token number">1024</span><span class="token punctuation">;</span>

<span class="token keyword">CACHE</span> <span class="token keyword">INDEX</span> pt <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p0<span class="token punctuation">)</span> <span class="token keyword">IN</span> kc_fast<span class="token punctuation">;</span>
<span class="token keyword">CACHE</span> <span class="token keyword">INDEX</span> pt <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p1<span class="token punctuation">,</span> p3<span class="token punctuation">)</span> <span class="token keyword">IN</span> kc_slow<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The previous set of statements performs the following actions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Creates a partitioned table with 4 partitions; these
            partitions are automatically named
      <code class="literal">
       p0
      </code>
      ,
            ...,
      <code class="literal">
       p3
      </code>
      ; this table has an index named
      <code class="literal">
       i
      </code>
      on column
      <code class="literal">
       c1
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Creates 2 key caches named
      <code class="literal">
       kc_fast
      </code>
      and
      <code class="literal">
       kc_slow
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      Assigns the index for partition
      <code class="literal">
       p0
      </code>
      to the
      <code class="literal">
       kc_fast
      </code>
      key cache and the index for
            partitions
      <code class="literal">
       p1
      </code>
      and
      <code class="literal">
       p3
      </code>
      to the
      <code class="literal">
       kc_slow
      </code>
      key cache; the index for
            the remaining partition (
      <code class="literal">
       p2
      </code>
      ) uses the
            server's default key cache.
     </p>
    </li>
   </ul>
  </div>
  <p>
   If you wish instead to assign the indexes for all partitions in
        table
   <code class="literal">
    pt
   </code>
   to a single key cache named
   <code class="literal">
    kc_all
   </code>
   , you can use either of the following
        two statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24589436"><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">CACHE</span> <span class="token keyword">INDEX</span> pt <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><span class="token keyword">ALL</span><span class="token punctuation">)</span> <span class="token keyword">IN</span> kc_all<span class="token punctuation">;</span>

<span class="token keyword">CACHE</span> <span class="token keyword">INDEX</span> pt <span class="token keyword">IN</span> kc_all<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The two statements just shown are equivalent, and issuing either
        one has exactly the same effect. In other words, if you wish to
        assign indexes for all partitions of a partitioned table to the
        same key cache, the
   <code class="literal">
    PARTITION (ALL)
   </code>
   clause is
        optional.
  </p>
  <p>
   When assigning indexes for multiple partitions to a key cache,
        the partitions need not be contiguous, and you need not list
        their names in any particular order. Indexes for any partitions
        not explicitly assigned to a key cache automatically use the
        server default key cache.
  </p>
  <p>
   Index preloading is also supported for partitioned
   <code class="literal">
    MyISAM
   </code>
   tables. For more information, see
   <a class="xref" href="load-index.html" title="15.7.8.5 LOAD INDEX INTO CACHE Statement">
    Section 15.7.8.5, “LOAD INDEX INTO CACHE Statement”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
