<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-innodb-ft-index-table-table">
      </a>
      28.4.19 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045076751568">
  </a>
  <p>
   The
   <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>
   table
      provides information about the inverted index used to process text
      searches against the
   <code class="literal">
    FULLTEXT
   </code>
   index of an
   <code class="literal">
    InnoDB
   </code>
   table.
  </p>
  <p>
   This table is empty initially. Before querying it, set the value
      of the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_aux_table">
    <code class="literal">
     innodb_ft_aux_table
    </code>
   </a>
   system
      variable to the name (including the database name) of the table
      that contains the
   <code class="literal">
    FULLTEXT
   </code>
   index (for example,
   <code class="literal">
    test/articles
   </code>
   ).
  </p>
  <p>
   For related usage information and examples, 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>
  <p>
   The
   <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>
   table has
      these columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       WORD
      </code>
     </p>
     <p>
      A word extracted from the text of the columns that are part of
          a
      <code class="literal">
       FULLTEXT
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FIRST_DOC_ID
      </code>
     </p>
     <p>
      The first document ID in which this word appears in the
      <code class="literal">
       FULLTEXT
      </code>
      index.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LAST_DOC_ID
      </code>
     </p>
     <p>
      The last document ID in which this word appears in the
      <code class="literal">
       FULLTEXT
      </code>
      index.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DOC_COUNT
      </code>
     </p>
     <p>
      The number of rows in which this word appears in the
      <code class="literal">
       FULLTEXT
      </code>
      index. The same word can occur
          several times within the cache table, once for each
          combination of
      <code class="literal">
       DOC_ID
      </code>
      and
      <code class="literal">
       POSITION
      </code>
      values.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DOC_ID
      </code>
     </p>
     <p>
      The document ID of the row containing the word. This value
          might reflect the value of an ID column that you defined for
          the underlying table, or it can be a sequence value generated
          by
      <code class="literal">
       InnoDB
      </code>
      when the table contains no
          suitable column.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       POSITION
      </code>
     </p>
     <p>
      The position of this particular instance of the word within
          the relevant document identified by the
      <code class="literal">
       DOC_ID
      </code>
      value.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045076722448">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      This table is empty initially. Before querying it, set the
          value of the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_aux_table">
       <code class="literal">
        innodb_ft_aux_table
       </code>
      </a>
      system
          variable to the name (including the database name) of the
          table that contains the
      <code class="literal">
       FULLTEXT
      </code>
      index (for
          example,
      <code class="literal">
       test/articles
      </code>
      ). The following
          example demonstrates how to use the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_ft_aux_table">
       <code class="literal">
        innodb_ft_aux_table
       </code>
      </a>
      system
          variable to show information about a
      <code class="literal">
       FULLTEXT
      </code>
      index for a specified table.
          Before information for newly inserted rows appears in
      <code class="literal">
       INNODB_FT_INDEX_TABLE
      </code>
      , the
      <code class="literal">
       FULLTEXT
      </code>
      index cache must be flushed to
          disk. This is accomplished by running an
      <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
       <code class="literal">
        OPTIMIZE TABLE
       </code>
      </a>
      operation on the
          indexed table with the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_optimize_fulltext_only">
       <code class="literal">
        innodb_optimize_fulltext_only
       </code>
      </a>
      system variable enabled. (The example disables that variable
          again at the end because it is intended to be enabled only
          temporarily.)
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97746523"><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">USE</span> test<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> articles <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>
         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>
         body <span class="token datatype">TEXT</span><span class="token punctuation">,</span>
         <span class="token keyword">FULLTEXT</span> <span class="token punctuation">(</span>title<span class="token punctuation">,</span>body<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">INSERT</span> <span class="token keyword">INTO</span> articles <span class="token punctuation">(</span>title<span class="token punctuation">,</span>body<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
       <span class="token punctuation">(</span><span class="token string">'MySQL Tutorial'</span><span class="token punctuation">,</span><span class="token string">'DBMS stands for DataBase ...'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'How To Use MySQL Well'</span><span class="token punctuation">,</span><span class="token string">'After you went through a ...'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'Optimizing MySQL'</span><span class="token punctuation">,</span><span class="token string">'In this tutorial we show ...'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'1001 MySQL Tricks'</span><span class="token punctuation">,</span><span class="token string">'1. Never run mysqld as root. 2. ...'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'MySQL vs. YourSQL'</span><span class="token punctuation">,</span><span class="token string">'In the following database comparison ...'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
       <span class="token punctuation">(</span><span class="token string">'MySQL Security'</span><span class="token punctuation">,</span><span class="token string">'When configured properly, MySQL ...'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> innodb_optimize_fulltext_only<span class="token operator">=</span><span class="token keyword">ON</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">OPTIMIZE</span> <span class="token keyword">TABLE</span> articles<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>
<span class="token output"><span class="token punctuation">|</span> Table         <span class="token punctuation">|</span> Op       <span class="token punctuation">|</span> Msg_type <span class="token punctuation">|</span> Msg_text <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> test.articles <span class="token punctuation">|</span> optimize <span class="token punctuation">|</span> status   <span class="token punctuation">|</span> OK       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> innodb_ft_aux_table <span class="token operator">=</span> <span class="token string">'test/articles'</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> WORD<span class="token punctuation">,</span> DOC_COUNT<span class="token punctuation">,</span> DOC_ID<span class="token punctuation">,</span> POSITION
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_FT_INDEX_TABLE <span class="token keyword">LIMIT</span> <span class="token number">5</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>
<span class="token output"><span class="token punctuation">|</span> WORD       <span class="token punctuation">|</span> DOC_COUNT <span class="token punctuation">|</span> DOC_ID <span class="token punctuation">|</span> POSITION <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>
<span class="token output"><span class="token punctuation">|</span> 1001       <span class="token punctuation">|</span>         1 <span class="token punctuation">|</span>      4 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> after      <span class="token punctuation">|</span>         1 <span class="token punctuation">|</span>      2 <span class="token punctuation">|</span>       22 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> comparison <span class="token punctuation">|</span>         1 <span class="token punctuation">|</span>      5 <span class="token punctuation">|</span>       44 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> configured <span class="token punctuation">|</span>         1 <span class="token punctuation">|</span>      6 <span class="token punctuation">|</span>       20 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> database   <span class="token punctuation">|</span>         2 <span class="token punctuation">|</span>      1 <span class="token punctuation">|</span>       31 <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> innodb_optimize_fulltext_only<span class="token operator">=</span><span class="token keyword">OFF</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      You must have the
      <a class="link" href="privileges-provided.html#priv_process">
       <code class="literal">
        PROCESS
       </code>
      </a>
      privilege to query this table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
      table or the
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW COLUMNS
       </code>
      </a>
      statement to view
          additional information about the columns of this table,
          including data types and default values.
     </p>
    </li>
    <li class="listitem">
     <p>
      For more information about
      <code class="literal">
       InnoDB
      </code>
      <code class="literal">
       FULLTEXT
      </code>
      search, see
      <a class="xref" href="innodb-fulltext-index.html" title="17.6.2.4 InnoDB Full-Text Indexes">
       Section 17.6.2.4, “InnoDB Full-Text Indexes”
      </a>
      , and
      <a class="xref" href="fulltext-search.html" title="14.9 Full-Text Search Functions">
       Section 14.9, “Full-Text Search Functions”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
