<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="column-indexes">
      </a>
      10.3.5 Column Indexes
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045227471248">
  </a>
  <a class="indexterm" name="idm46045227469760">
  </a>
  <a class="indexterm" name="idm46045227468272">
  </a>
  <p>
   The most common type of index involves a single column, storing
        copies of the values from that column in a data structure,
        allowing fast lookups for the rows with the corresponding column
        values. The B-tree data structure lets the index quickly find a
        specific value, a set of values, or a range of values,
        corresponding to operators such as
   <code class="literal">
    =
   </code>
   ,
   <code class="literal">
    &gt;
   </code>
   ,
   <code class="literal">
    ≤
   </code>
   ,
   <code class="literal">
    BETWEEN
   </code>
   ,
   <code class="literal">
    IN
   </code>
   , and so on, in
        a
   <code class="literal">
    WHERE
   </code>
   clause.
  </p>
  <p>
   The maximum number of indexes per table and the maximum index
        length is defined per storage engine. See
   <a class="xref" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    Chapter 17,
    <i>
     The InnoDB Storage Engine
    </i>
   </a>
   , and
   <a class="xref" href="storage-engines.html" title="Chapter 18 Alternative Storage Engines">
    Chapter 18,
    <i>
     Alternative Storage Engines
    </i>
   </a>
   . All storage engines support
        at least 16 indexes per table and a total index length of at
        least 256 bytes. Most storage engines have higher limits.
  </p>
  <p>
   For additional information about column indexes, see
   <a class="xref" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    Section 15.1.15, “CREATE INDEX Statement”
   </a>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="column-indexes.html#column-indexes-prefix" title="Index Prefixes">
       Index Prefixes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="column-indexes.html#column-indexes-fulltext" title="FULLTEXT Indexes">
       FULLTEXT Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="column-indexes.html#column-indexes-spatial" title="Spatial Indexes">
       Spatial Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="column-indexes.html#column-indexes-memory-storage-engine" title="Indexes in the MEMORY Storage Engine">
       Indexes in the MEMORY Storage Engine
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="column-indexes-prefix">
       </a>
       Index Prefixes
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045227453408">
   </a>
   <a class="indexterm" name="idm46045227451920">
   </a>
   <a class="indexterm" name="idm46045227450432">
   </a>
   <a class="indexterm" name="idm46045227448944">
   </a>
   <a class="indexterm" name="idm46045227447456">
   </a>
   <p>
    With
    <code class="literal">
     <em class="replaceable">
      <code>
       col_name
      </code>
     </em>
     (
     <em class="replaceable">
      <code>
       N
      </code>
     </em>
     )
    </code>
    syntax in an index specification for a string column, you can
          create an index that uses only the first
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    characters of the column.
          Indexing only a prefix of column values in this way can make
          the index file much smaller. When you index a
    <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>
    column, you
    <span class="emphasis">
     <em>
      must
     </em>
    </span>
    specify a prefix length for the
          index. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa39375566"><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>blob_col <span class="token datatype">BLOB</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>blob_col<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Prefixes can be up to 767 bytes long for
    <code class="literal">
     InnoDB
    </code>
    tables that use the
    <code class="literal">
     <a class="link" href="glossary.html#glos_redundant_row_format" title="redundant row format">
      REDUNDANT
     </a>
    </code>
    or
    <code class="literal">
     <a class="link" href="glossary.html#glos_compact_row_format" title="compact row format">
      COMPACT
     </a>
    </code>
    row format. The prefix length limit is 3072 bytes for
    <code class="literal">
     InnoDB
    </code>
    tables that use the
    <code class="literal">
     <a class="link" href="glossary.html#glos_dynamic_row_format" title="dynamic row format">
      DYNAMIC
     </a>
    </code>
    or
    <code class="literal">
     <a class="link" href="glossary.html#glos_compressed_row_format" title="compressed row format">
      COMPRESSED
     </a>
    </code>
    row format. For MyISAM tables, the prefix length limit is 1000
          bytes.
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     Prefix limits are measured in bytes, whereas the prefix
            length in
     <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
      <code class="literal">
       CREATE TABLE
      </code>
     </a>
     ,
     <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
      <code class="literal">
       ALTER TABLE
      </code>
     </a>
     , and
     <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
      <code class="literal">
       CREATE INDEX
      </code>
     </a>
     statements is
            interpreted as number of characters for nonbinary string
            types (
     <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
      <code class="literal">
       CHAR
      </code>
     </a>
     ,
     <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
      <code class="literal">
       VARCHAR
      </code>
     </a>
     ,
     <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
      <code class="literal">
       TEXT
      </code>
     </a>
     ) and number of bytes for
            binary string types (
     <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
      <code class="literal">
       BINARY
      </code>
     </a>
     ,
     <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
      <code class="literal">
       VARBINARY
      </code>
     </a>
     ,
     <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
      <code class="literal">
       BLOB
      </code>
     </a>
     ). Take this into account
            when specifying a prefix length for a nonbinary string
            column that uses a multibyte character set.
    </p>
   </div>
   <p>
    If a search term exceeds the index prefix length, the index is
          used to exclude non-matching rows, and the remaining rows are
          examined for possible matches.
   </p>
   <p>
    For additional information about index prefixes, see
    <a class="xref" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
     Section 15.1.15, “CREATE INDEX Statement”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="column-indexes-fulltext">
       </a>
       FULLTEXT Indexes
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045227416720">
   </a>
   <a class="indexterm" name="idm46045227415232">
   </a>
   <p>
    <code class="literal">
     FULLTEXT
    </code>
    indexes are used for full-text
          searches. Only the
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    and
    <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
     <code class="literal">
      MyISAM
     </code>
    </a>
    storage engines support
    <code class="literal">
     FULLTEXT
    </code>
    indexes and only for
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      CHAR
     </code>
    </a>
    ,
    <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="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      TEXT
     </code>
    </a>
    columns. Indexing always
          takes place over the entire column and column prefix indexing
          is not supported. For details, see
    <a class="xref" href="fulltext-search.html" title="14.9 Full-Text Search Functions">
     Section 14.9, “Full-Text Search Functions”
    </a>
    .
   </p>
   <p>
    Optimizations are applied to certain kinds of
    <code class="literal">
     FULLTEXT
    </code>
    queries against single
    <code class="literal">
     InnoDB
    </code>
    tables. Queries with these
          characteristics are particularly efficient:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        FULLTEXT
       </code>
       queries that only return the
              document ID, or the document ID and the search rank.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        FULLTEXT
       </code>
       queries that sort the matching
              rows in descending order of score and apply a
       <code class="literal">
        LIMIT
       </code>
       clause to take the top N matching
              rows. For this optimization to apply, there must be no
       <code class="literal">
        WHERE
       </code>
       clauses and only a single
       <code class="literal">
        ORDER BY
       </code>
       clause in descending order.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        FULLTEXT
       </code>
       queries that retrieve only the
       <code class="literal">
        COUNT(*)
       </code>
       value of rows matching a
              search term, with no additional
       <code class="literal">
        WHERE
       </code>
       clauses. Code the
       <code class="literal">
        WHERE
       </code>
       clause as
       <code class="literal">
        WHERE MATCH(
        <em class="replaceable">
         <code>
          text
         </code>
        </em>
        )
              AGAINST
              ('
        <em class="replaceable">
         <code>
          other_text
         </code>
        </em>
        ')
       </code>
       ,
              without any
       <code class="literal">
        &gt; 0
       </code>
       comparison operator.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For queries that contain full-text expressions, MySQL
          evaluates those expressions during the optimization phase of
          query execution. The optimizer does not just look at full-text
          expressions and make estimates, it actually evaluates them in
          the process of developing an execution plan.
   </p>
   <p>
    An implication of this behavior is that
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    for full-text queries
          is typically slower than for non-full-text queries for which
          no expression evaluation occurs during the optimization phase.
   </p>
   <p>
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    for full-text queries
          may show
    <code class="literal">
     Select tables optimized away
    </code>
    in
          the
    <code class="literal">
     Extra
    </code>
    column due to matching occurring
          during optimization; in this case, no table access need occur
          during later execution.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="column-indexes-spatial">
       </a>
       Spatial Indexes
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045227383552">
   </a>
   <a class="indexterm" name="idm46045227382064">
   </a>
   <p>
    You can create indexes on spatial data types.
    <code class="literal">
     MyISAM
    </code>
    and
    <code class="literal">
     InnoDB
    </code>
    support R-tree indexes on spatial types. Other storage engines
          use B-trees for indexing spatial types (except for
    <code class="literal">
     ARCHIVE
    </code>
    , which does not support spatial
          type indexing).
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="column-indexes-memory-storage-engine">
       </a>
       Indexes in the MEMORY Storage Engine
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045227376496">
   </a>
   <a class="indexterm" name="idm46045227375008">
   </a>
   <p>
    The
    <code class="literal">
     MEMORY
    </code>
    storage engine uses
    <code class="literal">
     HASH
    </code>
    indexes by default, but also supports
    <code class="literal">
     BTREE
    </code>
    indexes.
   </p>
  </div>
 </div>
 <br/>
</div>
