<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="generated-column-index-optimizations">
      </a>
      10.3.11 Optimizer Use of Generated Column Indexes
     </h3>
    </div>
   </div>
  </div>
  <p>
   MySQL supports indexes on generated columns. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa80370060"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>f1 <span class="token datatype">INT</span><span class="token punctuation">,</span> gc <span class="token datatype">INT</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>f1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>gc<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The generated column,
   <code class="literal">
    gc
   </code>
   , is defined as the
        expression
   <code class="literal">
    f1 + 1
   </code>
   . The column is also indexed
        and the optimizer can take that index into account during
        execution plan construction. In the following query, the
   <code class="literal">
    WHERE
   </code>
   clause refers to
   <code class="literal">
    gc
   </code>
   and the optimizer considers whether the index on that column
        yields a more efficient plan:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa13392312"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> gc <span class="token operator">&gt;</span> <span class="token number">9</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The optimizer can use indexes on generated columns to generate
        execution plans, even in the absence of direct references in
        queries to those columns by name. This occurs if the
   <code class="literal">
    WHERE
   </code>
   ,
   <code class="literal">
    ORDER BY
   </code>
   , or
   <code class="literal">
    GROUP BY
   </code>
   clause refers to an expression that
        matches the definition of some indexed generated column. The
        following query does not refer directly to
   <code class="literal">
    gc
   </code>
   but does use an expression that matches the definition of
   <code class="literal">
    gc
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa66816965"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> f1 <span class="token operator">+</span> <span class="token number">1</span> <span class="token operator">&gt;</span> <span class="token number">9</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The optimizer recognizes that the expression
   <code class="literal">
    f1 +
        1
   </code>
   matches the definition of
   <code class="literal">
    gc
   </code>
   and
        that
   <code class="literal">
    gc
   </code>
   is indexed, so it considers that
        index during execution plan construction. You can see this using
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13184508"><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">EXPLAIN</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> f1 <span class="token operator">+</span> <span class="token number">1</span> <span class="token operator">&gt;</span> <span class="token number">9</span>\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> t1
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> range
possible_keys<span class="token punctuation">:</span> gc
          key<span class="token punctuation">:</span> gc
      key_len<span class="token punctuation">:</span> 5
          ref<span class="token punctuation">:</span> NULL
         rows<span class="token punctuation">:</span> 1
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Using index condition</span></code></pre>
  </div>
  <p>
   In effect, the optimizer has replaced the expression
   <code class="literal">
    f1
        + 1
   </code>
   with the name of the generated column that matches
        the expression. That is also apparent in the rewritten query
        available in the extended
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   information displayed by
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW
        WARNINGS
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa85204224"><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">SHOW</span> <span class="token keyword">WARNINGS</span>\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
  Level<span class="token punctuation">:</span> Note
   Code<span class="token punctuation">:</span> 1003
Message<span class="token punctuation">:</span> /<span class="token punctuation">*</span> select#1 <span class="token punctuation">*</span>/ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
         AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` &gt; 9)</span></code></pre>
  </div>
  <p>
   The following restrictions and conditions apply to the
        optimizer's use of generated column indexes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      For a query expression to match a generated column
            definition, the expression must be identical and it must
            have the same result type. For example, if the generated
            column expression is
      <code class="literal">
       f1 + 1
      </code>
      , the
            optimizer does not recognize a match if the query uses
      <code class="literal">
       1 + f1
      </code>
      , or if
      <code class="literal">
       f1 + 1
      </code>
      (an integer expression) is compared with a string.
     </p>
    </li>
    <li class="listitem">
     <p>
      The optimization applies to these operators:
      <a class="link" href="comparison-operators.html#operator_equal">
       <code class="literal">
        =
       </code>
      </a>
      ,
      <a class="link" href="comparison-operators.html#operator_less-than">
       <code class="literal">
        &lt;
       </code>
      </a>
      ,
      <a class="link" href="comparison-operators.html#operator_less-than-or-equal">
       <code class="literal">
        &lt;=
       </code>
      </a>
      ,
      <a class="link" href="comparison-operators.html#operator_greater-than">
       <code class="literal">
        &gt;
       </code>
      </a>
      ,
      <a class="link" href="comparison-operators.html#operator_greater-than-or-equal">
       <code class="literal">
        &gt;=
       </code>
      </a>
      ,
      <a class="link" href="comparison-operators.html#operator_between">
       <code class="literal">
        BETWEEN
       </code>
      </a>
      , and
      <a class="link" href="comparison-operators.html#operator_in">
       <code class="literal">
        IN()
       </code>
      </a>
      .
     </p>
     <p>
      For operators other than
      <a class="link" href="comparison-operators.html#operator_between">
       <code class="literal">
        BETWEEN
       </code>
      </a>
      and
      <a class="link" href="comparison-operators.html#operator_in">
       <code class="literal">
        IN()
       </code>
      </a>
      , either operand can be
            replaced by a matching generated column. For
      <a class="link" href="comparison-operators.html#operator_between">
       <code class="literal">
        BETWEEN
       </code>
      </a>
      and
      <a class="link" href="comparison-operators.html#operator_in">
       <code class="literal">
        IN()
       </code>
      </a>
      , only the first argument
            can be replaced by a matching generated column, and the
            other arguments must have the same result type.
      <a class="link" href="comparison-operators.html#operator_between">
       <code class="literal">
        BETWEEN
       </code>
      </a>
      and
      <a class="link" href="comparison-operators.html#operator_in">
       <code class="literal">
        IN()
       </code>
      </a>
      are not yet supported for
            comparisons involving JSON values.
     </p>
    </li>
    <li class="listitem">
     <p>
      The generated column must be defined as an expression that
            contains at least a function call or one of the operators
            mentioned in the preceding item. The expression cannot
            consist of a simple reference to another column. For
            example,
      <code class="literal">
       gc INT AS (f1) STORED
      </code>
      consists
            only of a column reference, so indexes on
      <code class="literal">
       gc
      </code>
      are not considered.
     </p>
    </li>
    <li class="listitem">
     <p>
      For comparisons of strings to indexed generated columns that
            compute a value from a JSON function that returns a quoted
            string,
      <a class="link" href="json-modification-functions.html#function_json-unquote">
       <code class="literal">
        JSON_UNQUOTE()
       </code>
      </a>
      is
            needed in the column definition to remove the extra quotes
            from the function value. (For direct comparison of a string
            to the function result, the JSON comparator handles quote
            removal, but this does not occur for index lookups.) For
            example, instead of writing a column definition like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa99496890"><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">doc_name <span class="token datatype">TEXT</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token function">JSON_EXTRACT</span><span class="token punctuation">(</span>jdoc<span class="token punctuation">,</span> <span class="token string">'$.name'</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span></code></pre>
     </div>
     <p>
      Write it like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa84221709"><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">doc_name <span class="token datatype">TEXT</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token function">JSON_UNQUOTE</span><span class="token punctuation">(</span><span class="token function">JSON_EXTRACT</span><span class="token punctuation">(</span>jdoc<span class="token punctuation">,</span> <span class="token string">'$.name'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span></code></pre>
     </div>
     <p>
      With the latter definition, the optimizer can detect a match
            for both of these comparisons:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa39569906"><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 punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <span class="token function">JSON_EXTRACT</span><span class="token punctuation">(</span>jdoc<span class="token punctuation">,</span> <span class="token string">'$.name'</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">some_string</em>'</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">WHERE</span> <span class="token function">JSON_UNQUOTE</span><span class="token punctuation">(</span><span class="token function">JSON_EXTRACT</span><span class="token punctuation">(</span>jdoc<span class="token punctuation">,</span> <span class="token string">'$.name'</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">'<em class="replaceable">some_string</em>'</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
     </div>
     <p>
      Without
      <a class="link" href="json-modification-functions.html#function_json-unquote">
       <code class="literal">
        JSON_UNQUOTE()
       </code>
      </a>
      in the
            column definition, the optimizer detects a match only for
            the first of those comparisons.
     </p>
    </li>
    <li class="listitem">
     <p>
      If the optimizer picks the wrong index, an index hint can be
            used to disable it and force the optimizer to make a
            different choice.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
