<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysql-indexes">
      </a>
      10.3.1 How MySQL Uses Indexes
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045227571840">
  </a>
  <p>
   Indexes are used to find rows with specific column values
        quickly. Without an index, MySQL must begin with the first row
        and then read through the entire table to find the relevant
        rows. The larger the table, the more this costs. If the table
        has an index for the columns in question, MySQL can quickly
        determine the position to seek to in the middle of the data file
        without having to look at all the data. This is much faster than
        reading every row sequentially.
  </p>
  <p>
   Most MySQL indexes (
   <code class="literal">
    PRIMARY KEY
   </code>
   ,
   <code class="literal">
    UNIQUE
   </code>
   ,
   <code class="literal">
    INDEX
   </code>
   , and
   <code class="literal">
    FULLTEXT
   </code>
   ) are stored in
   <a class="link" href="glossary.html#glos_b_tree" title="B-tree">
    B-trees
   </a>
   . Exceptions: Indexes
        on spatial data types use R-trees;
   <code class="literal">
    MEMORY
   </code>
   tables also support
   <a class="link" href="glossary.html#glos_hash_index" title="hash index">
    hash
        indexes
   </a>
   ;
   <code class="literal">
    InnoDB
   </code>
   uses inverted lists
        for
   <code class="literal">
    FULLTEXT
   </code>
   indexes.
  </p>
  <p>
   In general, indexes are used as described in the following
        discussion. Characteristics specific to hash indexes (as used in
   <code class="literal">
    MEMORY
   </code>
   tables) are described in
   <a class="xref" href="index-btree-hash.html" title="10.3.9 Comparison of B-Tree and Hash Indexes">
    Section 10.3.9, “Comparison of B-Tree and Hash Indexes”
   </a>
   .
  </p>
  <p>
   MySQL uses indexes for these operations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To find the rows matching a
      <code class="literal">
       WHERE
      </code>
      clause
            quickly.
     </p>
    </li>
    <li class="listitem">
     <p>
      To eliminate rows from consideration. If there is a choice
            between multiple indexes, MySQL normally uses the index that
            finds the smallest number of rows (the most
      <a class="link" href="glossary.html#glos_selectivity" title="selectivity">
       selective
      </a>
      index).
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="indexterm" name="idm46045227555088">
      </a>
      <a class="indexterm" name="idm46045227553600">
      </a>
      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>
      . For more information, see
      <a class="xref" href="multiple-column-indexes.html" title="10.3.6 Multiple-Column Indexes">
       Section 10.3.6, “Multiple-Column Indexes”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      To retrieve rows from other tables when performing joins.
            MySQL can use indexes on columns more efficiently if they
            are declared as the same type and size. In this context,
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      and
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR
       </code>
      </a>
      are considered the same
            if they are declared as the same size. For example,
      <code class="literal">
       VARCHAR(10)
      </code>
      and
      <code class="literal">
       CHAR(10)
      </code>
      are the same size, but
      <code class="literal">
       VARCHAR(10)
      </code>
      and
      <code class="literal">
       CHAR(15)
      </code>
      are not.
     </p>
     <p>
      For comparisons between nonbinary string columns, both
            columns should use the same character set. For example,
            comparing a
      <code class="literal">
       utf8mb4
      </code>
      column with a
      <code class="literal">
       latin1
      </code>
      column precludes use of an index.
     </p>
     <p>
      Comparison of dissimilar columns (comparing a string column
            to a temporal or numeric column, for example) may prevent
            use of indexes if values cannot be compared directly without
            conversion. For a given value such as
      <code class="literal">
       1
      </code>
      in the numeric column, it might compare equal to any number
            of values in the string column such as
      <code class="literal">
       '1'
      </code>
      ,
      <code class="literal">
       ' 1'
      </code>
      ,
      <code class="literal">
       '00001'
      </code>
      , or
      <code class="literal">
       '01.e1'
      </code>
      .
            This rules out use of any indexes for the string column.
     </p>
    </li>
    <li class="listitem">
     <p>
      To find the
      <a class="link" href="aggregate-functions.html#function_min">
       <code class="literal">
        MIN()
       </code>
      </a>
      or
      <a class="link" href="aggregate-functions.html#function_max">
       <code class="literal">
        MAX()
       </code>
      </a>
      value for a specific
            indexed column
      <em class="replaceable">
       <code>
        key_col
       </code>
      </em>
      . This is
            optimized by a preprocessor that checks whether you are
            using
      <code class="literal">
       WHERE
       <em class="replaceable">
        <code>
         key_part_N
        </code>
       </em>
       =
       <em class="replaceable">
        <code>
         constant
        </code>
       </em>
      </code>
      on all key
            parts that occur before
      <em class="replaceable">
       <code>
        key_col
       </code>
      </em>
      in the index. In this case, MySQL does a single key lookup
            for each
      <a class="link" href="aggregate-functions.html#function_min">
       <code class="literal">
        MIN()
       </code>
      </a>
      or
      <a class="link" href="aggregate-functions.html#function_max">
       <code class="literal">
        MAX()
       </code>
      </a>
      expression and replaces
            it with a constant. If all expressions are replaced with
            constants, the query returns at once. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33297845"><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 function">MIN</span><span class="token punctuation">(</span><em class="replaceable">key_part2</em><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token function">MAX</span><span class="token punctuation">(</span><em class="replaceable">key_part2</em><span class="token punctuation">)</span>
  <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em><span class="token operator">=</span><span class="token number">10</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      To sort or group a table if the sorting or grouping is done
            on a leftmost prefix of a usable index (for example,
      <code class="literal">
       ORDER BY
       <em class="replaceable">
        <code>
         key_part1
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         key_part2
        </code>
       </em>
      </code>
      ). If all key
            parts are followed by
      <code class="literal">
       DESC
      </code>
      , the key is
            read in reverse order. (Or, if the index is a descending
            index, the key is read in forward order.) See
      <a class="xref" href="order-by-optimization.html" title="10.2.1.16 ORDER BY Optimization">
       Section 10.2.1.16, “ORDER BY Optimization”
      </a>
      ,
      <a class="xref" href="group-by-optimization.html" title="10.2.1.17 GROUP BY Optimization">
       Section 10.2.1.17, “GROUP BY Optimization”
      </a>
      , and
      <a class="xref" href="descending-indexes.html" title="10.3.13 Descending Indexes">
       Section 10.3.13, “Descending Indexes”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      In some cases, a query can be optimized to retrieve values
            without consulting the data rows. (An index that provides
            all the necessary results for a query is called a
      <a class="link" href="glossary.html#glos_covering_index" title="covering index">
       covering index
      </a>
      .)
            If a query uses from a table only columns that are included
            in some index, the selected values can be retrieved from the
            index tree for greater speed:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63379415"><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> <em class="replaceable">key_part3</em> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em>
  <span class="token keyword">WHERE</span> <em class="replaceable">key_part1</em><span class="token operator">=</span><span class="token number">1</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Indexes are less important for queries on small tables, or big
        tables where report queries process most or all of the rows.
        When a query needs to access most of the rows, reading
        sequentially is faster than working through an index. Sequential
        reads minimize disk seeks, even if not all the rows are needed
        for the query. See
   <a class="xref" href="table-scan-avoidance.html" title="10.2.1.23 Avoiding Full Table Scans">
    Section 10.2.1.23, “Avoiding Full Table Scans”
   </a>
   for
        details.
  </p>
 </div>
 <br/>
</div>
