<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-fulltext-index">
      </a>
      17.6.2.4 InnoDB Full-Text Indexes
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045165777968">
  </a>
  <a class="indexterm" name="idm46045165776512">
  </a>
  <p>
   Full-text indexes are created on text-based columns
      (
   <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>
   , or
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   columns) to speed up queries
      and DML operations on data contained within those columns.
  </p>
  <p>
   A full-text index is defined as part of a
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement or added to
      an existing table using
   <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="create-index.html" title="15.1.15 CREATE INDEX Statement">
    <code class="literal">
     CREATE INDEX
    </code>
   </a>
   .
  </p>
  <p>
   Full-text search is performed using
   <a class="link" href="fulltext-search.html#function_match">
    <code class="literal">
     MATCH()
      ... AGAINST
    </code>
   </a>
   syntax. For usage information, 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>
   <code class="literal">
    InnoDB
   </code>
   full-text indexes are described under
      the following topics in this section:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-fulltext-index.html#innodb-fulltext-index-design" title="InnoDB Full-Text Index Design">
       InnoDB Full-Text Index Design
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-fulltext-index.html#innodb-fulltext-index-tables" title="InnoDB Full-Text Index Tables">
       InnoDB Full-Text Index Tables
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-fulltext-index.html#innodb-fulltext-index-cache" title="InnoDB Full-Text Index Cache">
       InnoDB Full-Text Index Cache
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-fulltext-index.html#innodb-fulltext-index-docid" title="InnoDB Full-Text Index DOC_ID and FTS_DOC_ID Column">
       InnoDB Full-Text Index DOC_ID and FTS_DOC_ID Column
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-fulltext-index.html#innodb-fulltext-index-deletion" title="InnoDB Full-Text Index Deletion Handling">
       InnoDB Full-Text Index Deletion Handling
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-fulltext-index.html#innodb-fulltext-index-transaction" title="InnoDB Full-Text Index Transaction Handling">
       InnoDB Full-Text Index Transaction Handling
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-fulltext-index.html#innodb-fulltext-index-monitoring" title="Monitoring InnoDB Full-Text Indexes">
       Monitoring InnoDB Full-Text Indexes
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-fulltext-index-design">
       </a>
       InnoDB Full-Text Index Design
      </h5>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     InnoDB
    </code>
    full-text indexes have an inverted
        index design. Inverted indexes store a list of words, and for
        each word, a list of documents that the word appears in. To
        support proximity search, position information for each word is
        also stored, as a byte offset.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-fulltext-index-tables">
       </a>
       InnoDB Full-Text Index Tables
      </h5>
     </div>
    </div>
   </div>
   <p>
    When an
    <code class="literal">
     InnoDB
    </code>
    full-text index is created, a
        set of index tables is created, as shown in the following
        example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67228080"><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> opening_lines <span class="token punctuation">(</span>
       id <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
       opening_line <span class="token datatype">TEXT</span><span class="token punctuation">(</span><span class="token number">500</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       author <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       title <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token keyword">FULLTEXT</span> idx <span class="token punctuation">(</span>opening_line<span class="token punctuation">)</span>
       <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> table_id<span class="token punctuation">,</span> <span class="token keyword">name</span><span class="token punctuation">,</span> space <span class="token keyword">from</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLES
       <span class="token keyword">WHERE</span> <span class="token keyword">name</span> <span class="token operator">LIKE</span> <span class="token string">'test/%'</span><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 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_id <span class="token punctuation">|</span> name                                               <span class="token punctuation">|</span> space <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 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>      333 <span class="token punctuation">|</span> test/fts_0000000000000147_00000000000001c9_index_1 <span class="token punctuation">|</span>   289 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      334 <span class="token punctuation">|</span> test/fts_0000000000000147_00000000000001c9_index_2 <span class="token punctuation">|</span>   290 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      335 <span class="token punctuation">|</span> test/fts_0000000000000147_00000000000001c9_index_3 <span class="token punctuation">|</span>   291 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      336 <span class="token punctuation">|</span> test/fts_0000000000000147_00000000000001c9_index_4 <span class="token punctuation">|</span>   292 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      337 <span class="token punctuation">|</span> test/fts_0000000000000147_00000000000001c9_index_5 <span class="token punctuation">|</span>   293 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      338 <span class="token punctuation">|</span> test/fts_0000000000000147_00000000000001c9_index_6 <span class="token punctuation">|</span>   294 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      330 <span class="token punctuation">|</span> test/fts_0000000000000147_being_deleted            <span class="token punctuation">|</span>   286 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      331 <span class="token punctuation">|</span> test/fts_0000000000000147_being_deleted_cache      <span class="token punctuation">|</span>   287 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      332 <span class="token punctuation">|</span> test/fts_0000000000000147_config                   <span class="token punctuation">|</span>   288 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      328 <span class="token punctuation">|</span> test/fts_0000000000000147_deleted                  <span class="token punctuation">|</span>   284 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      329 <span class="token punctuation">|</span> test/fts_0000000000000147_deleted_cache            <span class="token punctuation">|</span>   285 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>      327 <span class="token punctuation">|</span> test/opening_lines                                 <span class="token punctuation">|</span>   283 <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 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 first six index tables comprise the inverted index and are
        referred to as auxiliary index tables. When incoming documents
        are tokenized, the individual words (also referred to as
    <span class="quote">
     “
     <span class="quote">
      tokens
     </span>
     ”
    </span>
    ) are inserted into the index tables along
        with position information and an associated
    <code class="literal">
     DOC_ID
    </code>
    . The words are fully sorted and
        partitioned among the six index tables based on the character
        set sort weight of the word's first character.
   </p>
   <p>
    The inverted index is partitioned into six auxiliary index
        tables to support parallel index creation. By default, two
        threads tokenize, sort, and insert words and associated data
        into the index tables. The number of threads that perform this
        work is configurable using the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_sort_pll_degree">
     <code class="literal">
      innodb_ft_sort_pll_degree
     </code>
    </a>
    variable. Consider increasing the number of threads when
        creating full-text indexes on large tables.
   </p>
   <p>
    Auxiliary index table names are prefixed with
    <code class="literal">
     fts_
    </code>
    and postfixed with
    <code class="literal">
     index_
     <em class="replaceable">
      <code>
       #
      </code>
     </em>
    </code>
    . Each
        auxiliary index table is associated with the indexed table by a
        hex value in the auxiliary index table name that matches the
    <code class="literal">
     table_id
    </code>
    of the indexed table. For example,
        the
    <code class="literal">
     table_id
    </code>
    of the
    <code class="literal">
     test/opening_lines
    </code>
    table is
    <code class="literal">
     327
    </code>
    , for which the hex value is 0x147. As
        shown in the preceding example, the
    <span class="quote">
     “
     <span class="quote">
      147
     </span>
     ”
    </span>
    hex value
        appears in the names of auxiliary index tables that are
        associated with the
    <code class="literal">
     test/opening_lines
    </code>
    table.
   </p>
   <p>
    A hex value representing the
    <code class="literal">
     index_id
    </code>
    of the
        full-text index also appears in auxiliary index table names. For
        example, in the auxiliary table name
    <code class="literal">
     test/fts_0000000000000147_00000000000001c9_index_1
    </code>
    ,
        the hex value
    <code class="literal">
     1c9
    </code>
    has a decimal value of 457.
        The index defined on the
    <code class="literal">
     opening_lines
    </code>
    table
        (
    <code class="literal">
     idx
    </code>
    ) can be identified by querying the
        Information Schema
    <a class="link" href="information-schema-innodb-indexes-table.html" title="28.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table">
     <code class="literal">
      INNODB_INDEXES
     </code>
    </a>
    table for this value (457).
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56881970"><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">SELECT</span> index_id<span class="token punctuation">,</span> <span class="token keyword">name</span><span class="token punctuation">,</span> table_id<span class="token punctuation">,</span> space <span class="token keyword">from</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_INDEXES
       <span class="token keyword">WHERE</span> index_id<span class="token operator">=</span><span class="token number">457</span><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>
<span class="token output"><span class="token punctuation">|</span> index_id <span class="token punctuation">|</span> name <span class="token punctuation">|</span> table_id <span class="token punctuation">|</span> space <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>
<span class="token output"><span class="token punctuation">|</span>      457 <span class="token punctuation">|</span> idx  <span class="token punctuation">|</span>      327 <span class="token punctuation">|</span>   283 <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></code></pre>
   </div>
   <p>
    Index tables are stored in their own tablespace if the primary
        table is created in a
    <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
     file-per-table
    </a>
    tablespace. Otherwise, index tables are stored in the tablespace
        where the indexed table resides.
   </p>
   <p>
    The other index tables shown in the preceding example are
        referred to as common index tables and are used for deletion
        handling and storing the internal state of full-text indexes.
        Unlike the inverted index tables, which are created for each
        full-text index, this set of tables is common to all full-text
        indexes created on a particular table.
   </p>
   <p>
    Common index tables are retained even if full-text indexes are
        dropped. When a full-text index is dropped, the
    <code class="literal">
     FTS_DOC_ID
    </code>
    column that was created for the
        index is retained, as removing the
    <code class="literal">
     FTS_DOC_ID
    </code>
    column would require rebuilding the previously indexed table.
        Common index tables are required to manage the
    <code class="literal">
     FTS_DOC_ID
    </code>
    column.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        fts_*_deleted
       </code>
       and
       <code class="literal">
        fts_*_deleted_cache
       </code>
      </p>
      <p>
       Contain the document IDs (DOC_ID) for documents that are
            deleted but whose data is not yet removed from the full-text
            index. The
       <code class="literal">
        fts_*_deleted_cache
       </code>
       is the
            in-memory version of the
       <code class="literal">
        fts_*_deleted
       </code>
       table.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        fts_*_being_deleted
       </code>
       and
       <code class="literal">
        fts_*_being_deleted_cache
       </code>
      </p>
      <p>
       Contain the document IDs (DOC_ID) for documents that are
            deleted and whose data is currently in the process of being
            removed from the full-text index. The
       <code class="literal">
        fts_*_being_deleted_cache
       </code>
       table is the
            in-memory version of the
       <code class="literal">
        fts_*_being_deleted
       </code>
       table.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        fts_*_config
       </code>
      </p>
      <p>
       Stores information about the internal state of the full-text
            index. Most importantly, it stores the
       <code class="literal">
        FTS_SYNCED_DOC_ID
       </code>
       , which identifies
            documents that have been parsed and flushed to disk. In case
            of crash recovery,
       <code class="literal">
        FTS_SYNCED_DOC_ID
       </code>
       values are used to identify documents that have not been
            flushed to disk so that the documents can be re-parsed and
            added back to the full-text index cache. To view the data in
            this table, query the Information Schema
       <a class="link" href="information-schema-innodb-ft-config-table.html" title="28.4.15 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table">
        <code class="literal">
         INNODB_FT_CONFIG
        </code>
       </a>
       table.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-fulltext-index-cache">
       </a>
       InnoDB Full-Text Index Cache
      </h5>
     </div>
    </div>
   </div>
   <p>
    When a document is inserted, it is tokenized, and the individual
        words and associated data are inserted into the full-text index.
        This process, even for small documents, can result in numerous
        small insertions into the auxiliary index tables, making
        concurrent access to these tables a point of contention. To
        avoid this problem,
    <code class="literal">
     InnoDB
    </code>
    uses a full-text
        index cache to temporarily cache index table insertions for
        recently inserted rows. This in-memory cache structure holds
        insertions until the cache is full and then batch flushes them
        to disk (to the auxiliary index tables). You can query the
        Information Schema
    <a class="link" href="information-schema-innodb-ft-index-cache-table.html" title="28.4.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table">
     <code class="literal">
      INNODB_FT_INDEX_CACHE
     </code>
    </a>
    table to view
        tokenized data for recently inserted rows.
   </p>
   <p>
    The caching and batch flushing behavior avoids frequent updates
        to auxiliary index tables, which could result in concurrent
        access issues during busy insert and update times. The batching
        technique also avoids multiple insertions for the same word, and
        minimizes duplicate entries. Instead of flushing each word
        individually, insertions for the same word are merged and
        flushed to disk as a single entry, improving insertion
        efficiency while keeping auxiliary index tables as small as
        possible.
   </p>
   <p>
    The
    <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_cache_size">
     <code class="literal">
      innodb_ft_cache_size
     </code>
    </a>
    variable is used to configure the full-text index cache size (on
        a per-table basis), which affects how often the full-text index
        cache is flushed. You can also define a global full-text index
        cache size limit for all tables in a given instance using the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_total_cache_size">
     <code class="literal">
      innodb_ft_total_cache_size
     </code>
    </a>
    variable.
   </p>
   <p>
    The full-text index cache stores the same information as
        auxiliary index tables. However, the full-text index cache only
        caches tokenized data for recently inserted rows. The data that
        is already flushed to disk (to the auxiliary index tables) is
        not brought back into the full-text index cache when queried.
        The data in auxiliary index tables is queried directly, and
        results from the auxiliary index tables are merged with results
        from the full-text index cache before being returned.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-fulltext-index-docid">
       </a>
       InnoDB Full-Text Index DOC_ID and FTS_DOC_ID Column
      </h5>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     InnoDB
    </code>
    uses a unique document identifier
        referred to as the
    <code class="literal">
     DOC_ID
    </code>
    to map words in the
        full-text index to document records where the word appears. The
        mapping requires an
    <code class="literal">
     FTS_DOC_ID
    </code>
    column on the
        indexed table. If an
    <code class="literal">
     FTS_DOC_ID
    </code>
    column is not
        defined,
    <code class="literal">
     InnoDB
    </code>
    automatically adds a hidden
    <code class="literal">
     FTS_DOC_ID
    </code>
    column when the full-text index is
        created. The following example demonstrates this behavior.
   </p>
   <p>
    The following table definition does not include an
    <code class="literal">
     FTS_DOC_ID
    </code>
    column:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78274334"><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> opening_lines <span class="token punctuation">(</span>
       id <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
       opening_line <span class="token datatype">TEXT</span><span class="token punctuation">(</span><span class="token number">500</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       author <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       title <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span>
       <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    When you create a full-text index on the table using
    <code class="literal">
     CREATE FULLTEXT INDEX
    </code>
    syntax, a warning is
        returned which reports that
    <code class="literal">
     InnoDB
    </code>
    is
        rebuilding the table to add the
    <code class="literal">
     FTS_DOC_ID
    </code>
    column.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80651267"><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">FULLTEXT</span> <span class="token keyword">INDEX</span> idx <span class="token keyword">ON</span> opening_lines<span class="token punctuation">(</span>opening_line<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected, 1 warning (0.19 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 1</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</span><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 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> Level   <span class="token punctuation">|</span> Code <span class="token punctuation">|</span> Message                                          <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 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> Warning <span class="token punctuation">|</span>  124 <span class="token punctuation">|</span> InnoDB rebuilding table to add column FTS_DOC_ID <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 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 same warning is returned when using
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    to add a full-text
        index to a table that does not have an
    <code class="literal">
     FTS_DOC_ID
    </code>
    column. If you create a full-text
        index at
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    time and do
        not specify an
    <code class="literal">
     FTS_DOC_ID
    </code>
    column,
    <code class="literal">
     InnoDB
    </code>
    adds a hidden
    <code class="literal">
     FTS_DOC_ID
    </code>
    column, without warning.
   </p>
   <p>
    Defining an
    <code class="literal">
     FTS_DOC_ID
    </code>
    column at
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    time is less
        expensive than creating a full-text index on a table that is
        already loaded with data. If an
    <code class="literal">
     FTS_DOC_ID
    </code>
    column is defined on a table prior to loading data, the table
        and its indexes do not have to be rebuilt to add the new column.
        If you are not concerned with
    <code class="literal">
     CREATE FULLTEXT
        INDEX
    </code>
    performance, leave out the
    <code class="literal">
     FTS_DOC_ID
    </code>
    column to have
    <code class="literal">
     InnoDB
    </code>
    create it for you.
    <code class="literal">
     InnoDB
    </code>
    creates a hidden
    <code class="literal">
     FTS_DOC_ID
    </code>
    column along with a unique index
        (
    <code class="literal">
     FTS_DOC_ID_INDEX
    </code>
    ) on the
    <code class="literal">
     FTS_DOC_ID
    </code>
    column. If you want to create your
        own
    <code class="literal">
     FTS_DOC_ID
    </code>
    column, the column must be
        defined as
    <code class="literal">
     BIGINT UNSIGNED NOT NULL
    </code>
    and named
    <code class="literal">
     FTS_DOC_ID
    </code>
    (all uppercase), as in the
        following example:
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     The
     <code class="literal">
      FTS_DOC_ID
     </code>
     column does not need to be
          defined as an
     <code class="literal">
      AUTO_INCREMENT
     </code>
     column, but
          doing so could make loading data easier.
    </p>
   </div>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20273545"><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> opening_lines <span class="token punctuation">(</span>
       FTS_DOC_ID <span class="token datatype">BIGINT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
       opening_line <span class="token datatype">TEXT</span><span class="token punctuation">(</span><span class="token number">500</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       author <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       title <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span>
       <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If you choose to define the
    <code class="literal">
     FTS_DOC_ID
    </code>
    column
        yourself, you are responsible for managing the column to avoid
        empty or duplicate values.
    <code class="literal">
     FTS_DOC_ID
    </code>
    values
        cannot be reused, which means
    <code class="literal">
     FTS_DOC_ID
    </code>
    values must be ever increasing.
   </p>
   <p>
    Optionally, you can create the required unique
    <code class="literal">
     FTS_DOC_ID_INDEX
    </code>
    (all uppercase) on the
    <code class="literal">
     FTS_DOC_ID
    </code>
    column.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa8561766"><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">UNIQUE</span> <span class="token keyword">INDEX</span> FTS_DOC_ID_INDEX <span class="token keyword">on</span> opening_lines<span class="token punctuation">(</span>FTS_DOC_ID<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If you do not create the
    <code class="literal">
     FTS_DOC_ID_INDEX
    </code>
    ,
    <code class="literal">
     InnoDB
    </code>
    creates it automatically.
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     <code class="literal">
      FTS_DOC_ID_INDEX
     </code>
     cannot be defined as a
          descending index because the
     <code class="literal">
      InnoDB
     </code>
     SQL
          parser does not use descending indexes.
    </p>
   </div>
   <p>
    The permitted gap between the largest used
    <code class="literal">
     FTS_DOC_ID
    </code>
    value and new
    <code class="literal">
     FTS_DOC_ID
    </code>
    value is 65535.
   </p>
   <p>
    To avoid rebuilding the table, the
    <code class="literal">
     FTS_DOC_ID
    </code>
    column is retained when dropping a full-text index.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-fulltext-index-deletion">
       </a>
       InnoDB Full-Text Index Deletion Handling
      </h5>
     </div>
    </div>
   </div>
   <p>
    Deleting a record that has a full-text index column could result
        in numerous small deletions in the auxiliary index tables,
        making concurrent access to these tables a point of contention.
        To avoid this problem, the
    <code class="literal">
     DOC_ID
    </code>
    of a
        deleted document is logged in a special
    <code class="literal">
     FTS_*_DELETED
    </code>
    table whenever a record is
        deleted from an indexed table, and the indexed record remains in
        the full-text index. Before returning query results, information
        in the
    <code class="literal">
     FTS_*_DELETED
    </code>
    table is used to filter
        out deleted
    <code class="literal">
     DOC_ID
    </code>
    s. The benefit of this
        design is that deletions are fast and inexpensive. The drawback
        is that the size of the index is not immediately reduced after
        deleting records. To remove full-text index entries for deleted
        records, run
    <code class="literal">
     OPTIMIZE TABLE
    </code>
    on the indexed
        table with
    <a class="link" href="innodb-parameters.html#sysvar_innodb_optimize_fulltext_only">
     <code class="literal">
      innodb_optimize_fulltext_only=ON
     </code>
    </a>
    to rebuild the full-text index. For more information, see
    <a class="xref" href="fulltext-fine-tuning.html#fulltext-optimize" title="Optimizing InnoDB Full-Text Indexes">
     Optimizing InnoDB Full-Text Indexes
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-fulltext-index-transaction">
       </a>
       InnoDB Full-Text Index Transaction Handling
      </h5>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     InnoDB
    </code>
    full-text indexes have special
        transaction handling characteristics due its caching and batch
        processing behavior. Specifically, updates and insertions on a
        full-text index are processed at transaction commit time, which
        means that a full-text search can only see committed data. The
        following example demonstrates this behavior. The full-text
        search only returns a result after the inserted lines are
        committed.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa12278775"><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> opening_lines <span class="token punctuation">(</span>
       id <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
       opening_line <span class="token datatype">TEXT</span><span class="token punctuation">(</span><span class="token number">500</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       author <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       title <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">200</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token keyword">FULLTEXT</span> idx <span class="token punctuation">(</span>opening_line<span class="token punctuation">)</span>
       <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">BEGIN</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> opening_lines<span class="token punctuation">(</span>opening_line<span class="token punctuation">,</span>author<span class="token punctuation">,</span>title<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
       <span class="token punctuation">(</span><span class="token string">'Call me Ishmael.'</span><span class="token punctuation">,</span><span class="token string">'Herman Melville'</span><span class="token punctuation">,</span><span class="token string">'Moby-Dick'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'A screaming comes across the sky.'</span><span class="token punctuation">,</span><span class="token string">'Thomas Pynchon'</span><span class="token punctuation">,</span><span class="token string">'Gravity\'s Rainbow'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'I am an invisible man.'</span><span class="token punctuation">,</span><span class="token string">'Ralph Ellison'</span><span class="token punctuation">,</span><span class="token string">'Invisible Man'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'Where now? Who now? When now?'</span><span class="token punctuation">,</span><span class="token string">'Samuel Beckett'</span><span class="token punctuation">,</span><span class="token string">'The Unnamable'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'It was love at first sight.'</span><span class="token punctuation">,</span><span class="token string">'Joseph Heller'</span><span class="token punctuation">,</span><span class="token string">'Catch-22'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'All this happened, more or less.'</span><span class="token punctuation">,</span><span class="token string">'Kurt Vonnegut'</span><span class="token punctuation">,</span><span class="token string">'Slaughterhouse-Five'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'Mrs. Dalloway said she would buy the flowers herself.'</span><span class="token punctuation">,</span><span class="token string">'Virginia Woolf'</span><span class="token punctuation">,</span><span class="token string">'Mrs. Dalloway'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'It was a pleasure to burn.'</span><span class="token punctuation">,</span><span class="token string">'Ray Bradbury'</span><span class="token punctuation">,</span><span class="token string">'Fahrenheit 451'</span><span class="token punctuation">)</span><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><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> opening_lines <span class="token keyword">WHERE</span> <span class="token operator">MATCH</span><span class="token punctuation">(</span>opening_line<span class="token punctuation">)</span> <span class="token keyword">AGAINST</span><span class="token punctuation">(</span><span class="token string">'Ishmael'</span><span class="token punctuation">)</span><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>
<span class="token output"><span class="token punctuation">|</span> COUNT(*) <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>
<span class="token output"><span class="token punctuation">|</span>        0 <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">COMMIT</span><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><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> opening_lines 
<span class="token prompt">    -&gt;</span> <span class="token keyword">WHERE</span> <span class="token operator">MATCH</span><span class="token punctuation">(</span>opening_line<span class="token punctuation">)</span> <span class="token keyword">AGAINST</span><span class="token punctuation">(</span><span class="token string">'Ishmael'</span><span class="token punctuation">)</span><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>
<span class="token output"><span class="token punctuation">|</span> COUNT(*) <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>
<span class="token output"><span class="token punctuation">|</span>        1 <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></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-fulltext-index-monitoring">
       </a>
       Monitoring InnoDB Full-Text Indexes
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045165607728">
   </a>
   <a class="indexterm" name="idm46045165606240">
   </a>
   <p>
    You can monitor and examine the special text-processing aspects
        of
    <code class="literal">
     InnoDB
    </code>
    full-text indexes by querying the
        following
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
    tables:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="information-schema-innodb-ft-config-table.html" title="28.4.15 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table">
        <code class="literal">
         INNODB_FT_CONFIG
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="information-schema-innodb-ft-index-table-table.html" title="28.4.19 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table">
        <code class="literal">
         INNODB_FT_INDEX_TABLE
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="information-schema-innodb-ft-index-cache-table.html" title="28.4.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table">
        <code class="literal">
         INNODB_FT_INDEX_CACHE
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="information-schema-innodb-ft-default-stopword-table.html" title="28.4.16 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table">
        <code class="literal">
         INNODB_FT_DEFAULT_STOPWORD
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="information-schema-innodb-ft-deleted-table.html" title="28.4.17 The INFORMATION_SCHEMA INNODB_FT_DELETED Table">
        <code class="literal">
         INNODB_FT_DELETED
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="information-schema-innodb-ft-being-deleted-table.html" title="28.4.14 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table">
        <code class="literal">
         INNODB_FT_BEING_DELETED
        </code>
       </a>
      </p>
     </li>
    </ul>
   </div>
   <p>
    You can also view basic information for full-text indexes and
        tables by querying
    <a class="link" href="information-schema-innodb-indexes-table.html" title="28.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table">
     <code class="literal">
      INNODB_INDEXES
     </code>
    </a>
    and
    <a class="link" href="information-schema-innodb-tables-table.html" title="28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table">
     <code class="literal">
      INNODB_TABLES
     </code>
    </a>
    .
   </p>
   <p>
    For more information, see
    <a class="xref" href="innodb-information-schema-fulltext_index-tables.html" title="17.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables">
     Section 17.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
