<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="multiple-column-indexes">
      </a>
      10.3.6 Multiple-Column Indexes
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045227369536">
  </a>
  <a class="indexterm" name="idm46045227368464">
  </a>
  <a class="indexterm" name="idm46045227366976">
  </a>
  <p>
   MySQL can create composite indexes (that is, indexes on multiple
        columns). An index may consist of up to 16 columns. For certain
        data types, you can index a prefix of the column (see
   <a class="xref" href="column-indexes.html" title="10.3.5 Column Indexes">
    Section 10.3.5, “Column Indexes”
   </a>
   ).
  </p>
  <p>
   MySQL can use multiple-column indexes for queries that test all
        the columns in the index, or queries that test just the first
        column, the first two columns, the first three columns, and so
        on. If you specify the columns in the right order in the index
        definition, a single composite index can speed up several kinds
        of queries on the same table.
  </p>
  <p>
   A multiple-column index can be considered a sorted array, the
        rows of which contain values that are created by concatenating
        the values of the indexed columns.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    As an alternative to a composite index, you can introduce a
          column that is
    <span class="quote">
     “
     <span class="quote">
      hashed
     </span>
     ”
    </span>
    based on information from
          other columns. If this column is short, reasonably unique, and
          indexed, it might be faster than a
    <span class="quote">
     “
     <span class="quote">
      wide
     </span>
     ”
    </span>
    index
          on many columns. In MySQL, it is very easy to use this extra
          column:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa35762859"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">hash_col</em><span class="token operator">=</span><span class="token function">MD5</span><span class="token punctuation">(</span><span class="token function">CONCAT</span><span class="token punctuation">(</span><em class="replaceable">val1</em><span class="token punctuation">,</span><em class="replaceable">val2</em><span class="token punctuation">)</span><span class="token punctuation">)</span>
  <span class="token operator">AND</span> <em class="replaceable">col1</em><span class="token operator">=</span><em class="replaceable">val1</em> <span class="token operator">AND</span> <em class="replaceable">col2</em><span class="token operator">=</span><em class="replaceable">val2</em><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <p>
   Suppose that a table has the following specification:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43179551"><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> test <span class="token punctuation">(</span>
    id         <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    last_name  <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    first_name <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> <span class="token keyword">name</span> <span class="token punctuation">(</span>last_name<span class="token punctuation">,</span>first_name<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    name
   </code>
   index is an index over the
   <code class="literal">
    last_name
   </code>
   and
   <code class="literal">
    first_name
   </code>
   columns. The index can be used for lookups in queries that
        specify values in a known range for combinations of
   <code class="literal">
    last_name
   </code>
   and
   <code class="literal">
    first_name
   </code>
   values. It can also be used for queries that specify just a
   <code class="literal">
    last_name
   </code>
   value because that column is a
        leftmost prefix of the index (as described later in this
        section). Therefore, the
   <code class="literal">
    name
   </code>
   index is used
        for lookups in the following queries:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25230771"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test <span class="token keyword">WHERE</span> last_name<span class="token operator">=</span><span class="token string">'Jones'</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test
  <span class="token keyword">WHERE</span> last_name<span class="token operator">=</span><span class="token string">'Jones'</span> <span class="token operator">AND</span> first_name<span class="token operator">=</span><span class="token string">'John'</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test
  <span class="token keyword">WHERE</span> last_name<span class="token operator">=</span><span class="token string">'Jones'</span>
  <span class="token operator">AND</span> <span class="token punctuation">(</span>first_name<span class="token operator">=</span><span class="token string">'John'</span> <span class="token operator">OR</span> first_name<span class="token operator">=</span><span class="token string">'Jon'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test
  <span class="token keyword">WHERE</span> last_name<span class="token operator">=</span><span class="token string">'Jones'</span>
  <span class="token operator">AND</span> first_name <span class="token operator">&gt;=</span><span class="token string">'M'</span> <span class="token operator">AND</span> first_name <span class="token operator">&lt;</span> <span class="token string">'N'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   However, the
   <code class="literal">
    name
   </code>
   index is
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   used for lookups in the following
        queries:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa94625877"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test <span class="token keyword">WHERE</span> first_name<span class="token operator">=</span><span class="token string">'John'</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test
  <span class="token keyword">WHERE</span> last_name<span class="token operator">=</span><span class="token string">'Jones'</span> <span class="token operator">OR</span> first_name<span class="token operator">=</span><span class="token string">'John'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Suppose that you issue the following
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa91375606"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> col1<span class="token operator">=</span><em class="replaceable">val1</em> <span class="token operator">AND</span> col2<span class="token operator">=</span><em class="replaceable">val2</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If a multiple-column index exists on
   <code class="literal">
    col1
   </code>
   and
   <code class="literal">
    col2
   </code>
   , the appropriate rows can be fetched
        directly. If separate single-column indexes exist on
   <code class="literal">
    col1
   </code>
   and
   <code class="literal">
    col2
   </code>
   , the
        optimizer attempts to use the Index Merge optimization (see
   <a class="xref" href="index-merge-optimization.html" title="10.2.1.3 Index Merge Optimization">
    Section 10.2.1.3, “Index Merge Optimization”
   </a>
   ), or attempts to find
        the most restrictive index by deciding which index excludes more
        rows and using that index to fetch the rows.
  </p>
  <a class="indexterm" name="idm46045227336528">
  </a>
  <a class="indexterm" name="idm46045227335040">
  </a>
  <p>
   If the table has a multiple-column index, any leftmost prefix of
        the index can be used by the optimizer to look up rows. For
        example, if you have a three-column index on
   <code class="literal">
    (col1,
        col2, col3)
   </code>
   , you have indexed search capabilities on
   <code class="literal">
    (col1)
   </code>
   ,
   <code class="literal">
    (col1, col2)
   </code>
   , and
   <code class="literal">
    (col1, col2, col3)
   </code>
   .
  </p>
  <p>
   MySQL cannot use the index to perform lookups if the columns do
        not form a leftmost prefix of the index. Suppose that you have
        the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29841118"><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">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> col1<span class="token operator">=</span><em class="replaceable">val1</em><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> col1<span class="token operator">=</span><em class="replaceable">val1</em> <span class="token operator">AND</span> col2<span class="token operator">=</span><em class="replaceable">val2</em><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> col2<span class="token operator">=</span><em class="replaceable">val2</em><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> col2<span class="token operator">=</span><em class="replaceable">val2</em> <span class="token operator">AND</span> col3<span class="token operator">=</span><em class="replaceable">val3</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If an index exists on
   <code class="literal">
    (col1, col2, col3)
   </code>
   ,
        only the first two queries use the index. The third and fourth
        queries do involve indexed columns, but do not use an index to
        perform lookups because
   <code class="literal">
    (col2)
   </code>
   and
   <code class="literal">
    (col2, col3)
   </code>
   are not leftmost prefixes of
   <code class="literal">
    (col1, col2, col3)
   </code>
   .
  </p>
 </div>
 <br/>
</div>
