<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="create-index">
      </a>
      15.1.15 CREATE INDEX Statement
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045188100000">
  </a>
  <a class="indexterm" name="idm46045188098928">
  </a>
  <a class="indexterm" name="idm46045188097856">
  </a>
  <a class="indexterm" name="idm46045188096368">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78334747"><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 punctuation">[</span><span class="token keyword">UNIQUE</span> <span class="token operator">|</span> <span class="token keyword">FULLTEXT</span> <span class="token operator">|</span> <span class="token keyword">SPATIAL</span><span class="token punctuation">]</span> <span class="token keyword">INDEX</span> <em class="replaceable">index_name</em>
    <span class="token punctuation">[</span><em class="replaceable">index_type</em><span class="token punctuation">]</span>
    <span class="token keyword">ON</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span><em class="replaceable">key_part</em><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
    <span class="token punctuation">[</span><em class="replaceable">index_option</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><em class="replaceable">algorithm_option</em> <span class="token operator">|</span> <em class="replaceable">lock_option</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">key_part</em>: {<em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">length</em><span class="token punctuation">)</span><span class="token punctuation">]</span> <span class="token operator">|</span> <span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">)</span>} <span class="token punctuation">[</span><span class="token keyword">ASC</span> <span class="token operator">|</span> <span class="token keyword">DESC</span><span class="token punctuation">]</span>

<em class="replaceable">index_option</em>: {
    <span class="token keyword">KEY_BLOCK_SIZE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">value</em></span>
  <span class="token operator">|</span> <em class="replaceable">index_type</em>
  <span class="token operator">|</span> <span class="token keyword">WITH</span> <span class="token keyword">PARSER</span> <em class="replaceable">parser_name</em>
  <span class="token operator">|</span> <span class="token keyword">COMMENT</span> <span class="token string">'<em class="replaceable">string</em>'</span>
  <span class="token operator">|</span> {<span class="token keyword">VISIBLE</span> <span class="token operator">|</span> <span class="token keyword">INVISIBLE</span>}
  <span class="token operator">|</span> <span class="token keyword">ENGINE_ATTRIBUTE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span>
  <span class="token operator">|</span> <span class="token keyword">SECONDARY_ENGINE_ATTRIBUTE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span>
}

<em class="replaceable">index_type</em>:
    <span class="token keyword">USING</span> {<span class="token keyword">BTREE</span> <span class="token operator">|</span> <span class="token keyword">HASH</span>}

<em class="replaceable">algorithm_option</em>:
    <span class="token keyword">ALGORITHM</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token keyword">DEFAULT</span> <span class="token operator">|</span> <span class="token keyword">INPLACE</span> <span class="token operator">|</span> <span class="token keyword">COPY</span>}

<em class="replaceable">lock_option</em>:
    <span class="token keyword">LOCK</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token keyword">DEFAULT</span> <span class="token operator">|</span> <span class="token keyword">NONE</span> <span class="token operator">|</span> <span class="token keyword">SHARED</span> <span class="token operator">|</span> <span class="token keyword">EXCLUSIVE</span>}</code></pre>
  </div>
  <p>
   Normally, you create all indexes on a table at the time the table
      itself is created with
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
      TABLE
    </code>
   </a>
   . See
   <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    Section 15.1.20, “CREATE TABLE Statement”
   </a>
   . This
      guideline is especially important for
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   tables, where the primary key
      determines the physical layout of rows in the data file.
   <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    <code class="literal">
     CREATE INDEX
    </code>
   </a>
   enables you to add
      indexes to existing tables.
  </p>
  <p>
   <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    <code class="literal">
     CREATE INDEX
    </code>
   </a>
   is mapped to an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement to create
      indexes. See
   <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    Section 15.1.9, “ALTER TABLE Statement”
   </a>
   .
   <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    <code class="literal">
     CREATE INDEX
    </code>
   </a>
   cannot be used to
      create a
   <code class="literal">
    PRIMARY KEY
   </code>
   ; use
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   instead. For more
      information about indexes, see
   <a class="xref" href="mysql-indexes.html" title="10.3.1 How MySQL Uses Indexes">
    Section 10.3.1, “How MySQL Uses Indexes”
   </a>
   .
  </p>
  <p>
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   supports secondary indexes on
      virtual columns. For more information, see
   <a class="xref" href="create-table-secondary-indexes.html" title="15.1.20.9 Secondary Indexes and Generated Columns">
    Section 15.1.20.9, “Secondary Indexes and Generated Columns”
   </a>
   .
  </p>
  <p>
   When the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
    <code class="literal">
     innodb_stats_persistent
    </code>
   </a>
   setting is enabled, run the
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE
      TABLE
    </code>
   </a>
   statement for an
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   table after creating an index
      on that table.
  </p>
  <p>
   The
   <em class="replaceable">
    <code>
     expr
    </code>
   </em>
   for a
   <em class="replaceable">
    <code>
     key_part
    </code>
   </em>
   specification can also take
      the form
   <code class="literal">
    (CAST
    <em class="replaceable">
     <code>
      json_expression
     </code>
    </em>
    AS
    <em class="replaceable">
     <code>
      type
     </code>
    </em>
    ARRAY)
   </code>
   to create a
      multi-valued index on a
   <a class="link" href="json.html" title="13.5 The JSON Data Type">
    <code class="literal">
     JSON
    </code>
   </a>
   column. See
   <a class="xref" href="create-index.html#create-index-multi-valued" title="Multi-Valued Indexes">
    Multi-Valued Indexes
   </a>
   .
  </p>
  <p>
   An index specification of the form
   <code class="literal">
    (
    <em class="replaceable">
     <code>
      key_part1
     </code>
    </em>
    ,
    <em class="replaceable">
     <code>
      key_part2
     </code>
    </em>
    , ...)
   </code>
   creates an
      index with multiple key parts. Index key values are formed by
      concatenating the values of the given key parts. For example
   <code class="literal">
    (col1, col2, col3)
   </code>
   specifies a multiple-column
      index with index keys consisting of values from
   <code class="literal">
    col1
   </code>
   ,
   <code class="literal">
    col2
   </code>
   , and
   <code class="literal">
    col3
   </code>
   .
  </p>
  <p>
   A
   <em class="replaceable">
    <code>
     key_part
    </code>
   </em>
   specification can end with
   <code class="literal">
    ASC
   </code>
   or
   <code class="literal">
    DESC
   </code>
   to specify
      whether index values are stored in ascending or descending order.
      The default is ascending if no order specifier is given.
  </p>
  <p>
   <code class="literal">
    ASC
   </code>
   and
   <code class="literal">
    DESC
   </code>
   are not
      supported for
   <code class="literal">
    HASH
   </code>
   indexes, multi-valued
      indexes or
   <code class="literal">
    SPATIAL
   </code>
   indexes.
  </p>
  <p>
   The following sections describe different aspects of the
   <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    <code class="literal">
     CREATE INDEX
    </code>
   </a>
   statement:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="create-index.html#create-index-column-prefixes" title="Column Prefix Key Parts">
       Column Prefix Key Parts
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-index.html#create-index-functional-key-parts" title="Functional Key Parts">
       Functional Key Parts
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-index.html#create-index-unique" title="Unique Indexes">
       Unique Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-index.html#create-index-fulltext" title="Full-Text Indexes">
       Full-Text Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-index.html#create-index-multi-valued" title="Multi-Valued Indexes">
       Multi-Valued Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-index.html#create-index-spatial" title="Spatial Indexes">
       Spatial Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-index.html#create-index-options" title="Index Options">
       Index Options
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-index.html#create-index-copying" title="Table Copying and Locking Options">
       Table Copying and Locking Options
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="create-index-column-prefixes">
       </a>
       Column Prefix Key Parts
      </h4>
     </div>
    </div>
   </div>
   <p>
    For string columns, indexes can be created that use only the
        leading part of column values, using
    <code class="literal">
     <em class="replaceable">
      <code>
       col_name
      </code>
     </em>
     (
     <em class="replaceable">
      <code>
       length
      </code>
     </em>
     )
    </code>
    syntax to specify an index prefix length:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Prefixes can be specified for
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR
        </code>
       </a>
       ,
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       ,
       <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
        <code class="literal">
         BINARY
        </code>
       </a>
       , and
       <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
        <code class="literal">
         VARBINARY
        </code>
       </a>
       key parts.
      </p>
     </li>
     <li class="listitem">
      <p>
       Prefixes
       <span class="emphasis">
        <em>
         must
        </em>
       </span>
       be specified for
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         BLOB
        </code>
       </a>
       and
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         TEXT
        </code>
       </a>
       key parts. Additionally,
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         BLOB
        </code>
       </a>
       and
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         TEXT
        </code>
       </a>
       columns can be indexed
            only for
       <code class="literal">
        InnoDB
       </code>
       ,
       <code class="literal">
        MyISAM
       </code>
       , and
       <code class="literal">
        BLACKHOLE
       </code>
       tables.
      </p>
      <a class="indexterm" name="idm46045188019232">
      </a>
      <a class="indexterm" name="idm46045188017744">
      </a>
      <a class="indexterm" name="idm46045188016256">
      </a>
      <a class="indexterm" name="idm46045188014768">
      </a>
     </li>
     <li class="listitem">
      <p>
       Prefix
       <span class="emphasis">
        <em>
         limits
        </em>
       </span>
       are measured in bytes.
            However, prefix
       <span class="emphasis">
        <em>
         lengths
        </em>
       </span>
       for index
            specifications in
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE
            TABLE
        </code>
       </a>
       ,
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       ,
            and
       <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
        <code class="literal">
         CREATE INDEX
        </code>
       </a>
       statements
            are interpreted as number of characters for nonbinary string
            types (
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR
        </code>
       </a>
       ,
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       ,
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         TEXT
        </code>
       </a>
       ) and number of bytes for
            binary string types (
       <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
        <code class="literal">
         BINARY
        </code>
       </a>
       ,
       <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
        <code class="literal">
         VARBINARY
        </code>
       </a>
       ,
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         BLOB
        </code>
       </a>
       ). Take this into account
            when specifying a prefix length for a nonbinary string
            column that uses a multibyte character set.
      </p>
      <p>
       Prefix support and lengths of prefixes (where supported) are
            storage engine dependent. For example, a prefix can be up to
            767 bytes long for
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       tables that use the
       <code class="literal">
        <a class="link" href="glossary.html#glos_redundant_row_format" title="redundant row format">
         REDUNDANT
        </a>
       </code>
       or
       <code class="literal">
        <a class="link" href="glossary.html#glos_compact_row_format" title="compact row format">
         COMPACT
        </a>
       </code>
       row format. The prefix length limit is 3072 bytes for
       <code class="literal">
        InnoDB
       </code>
       tables that use the
       <code class="literal">
        <a class="link" href="glossary.html#glos_dynamic_row_format" title="dynamic row format">
         DYNAMIC
        </a>
       </code>
       or
       <code class="literal">
        <a class="link" href="glossary.html#glos_compressed_row_format" title="compressed row format">
         COMPRESSED
        </a>
       </code>
       row format. For
       <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
        <code class="literal">
         MyISAM
        </code>
       </a>
       tables,
            the prefix length limit is 1000 bytes. The
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       storage engine does not
            support prefixes (see
       <a class="xref" href="mysql-cluster-limitations-unsupported.html" title="25.2.7.6 Unsupported or Missing Features in NDB Cluster">
        Section 25.2.7.6, “Unsupported or Missing Features in NDB Cluster”
       </a>
       ).
      </p>
     </li>
    </ul>
   </div>
   <p>
    If a specified index prefix exceeds the maximum column data type
        size,
    <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
     <code class="literal">
      CREATE INDEX
     </code>
    </a>
    handles the
        index as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For a nonunique index, either an error occurs (if strict SQL
            mode is enabled), or the index length is reduced to lie
            within the maximum column data type size and a warning is
            produced (if strict SQL mode is not enabled).
      </p>
     </li>
     <li class="listitem">
      <p>
       For a unique index, an error occurs regardless of SQL mode
            because reducing the index length might enable insertion of
            nonunique entries that do not meet the specified uniqueness
            requirement.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The statement shown here creates an index using the first 10
        characters of the
    <code class="literal">
     name
    </code>
    column (assuming that
    <code class="literal">
     name
    </code>
    has a nonbinary string type):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa87450387"><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">INDEX</span> part_of_name <span class="token keyword">ON</span> customer <span class="token punctuation">(</span><span class="token keyword">name</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If names in the column usually differ in the first 10
        characters, lookups performed using this index should not be
        much slower than using an index created from the entire
    <code class="literal">
     name
    </code>
    column. Also, using column prefixes for
        indexes can make the index file much smaller, which could save a
        lot of disk space and might also speed up
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    operations.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="create-index-functional-key-parts">
       </a>
       Functional Key Parts
      </h4>
     </div>
    </div>
   </div>
   <p>
    A
    <span class="quote">
     “
     <span class="quote">
      normal
     </span>
     ”
    </span>
    index indexes column values or prefixes
        of column values. For example, in the following table, the index
        entry for a given
    <code class="literal">
     t1
    </code>
    row includes the full
    <code class="literal">
     col1
    </code>
    value and a prefix of the
    <code class="literal">
     col2
    </code>
    value consisting of its first 10
        characters:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55269619"><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>
  col1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  col2 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>col1<span class="token punctuation">,</span> col2<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Functional key parts that index expression values canalso be
        used in place of column or column prefix values. Use of
        functional key parts enables indexing of values not stored
        directly in the table. Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa94567510"><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>col1 <span class="token datatype">INT</span><span class="token punctuation">,</span> col2 <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span> func_index <span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token function">ABS</span><span class="token punctuation">(</span>col1<span class="token punctuation">)</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">CREATE</span> <span class="token keyword">INDEX</span> idx1 <span class="token keyword">ON</span> t1 <span class="token punctuation">(</span><span class="token punctuation">(</span>col1 <span class="token operator">+</span> col2<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> idx2 <span class="token keyword">ON</span> t1 <span class="token punctuation">(</span><span class="token punctuation">(</span>col1 <span class="token operator">+</span> col2<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>col1 <span class="token operator">-</span> col2<span class="token punctuation">)</span><span class="token punctuation">,</span> col1<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span><span class="token punctuation">(</span>col1 <span class="token operator">*</span> <span class="token number">40</span><span class="token punctuation">)</span> <span class="token keyword">DESC</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    An index with multiple key parts can mix nonfunctional and
        functional key parts.
   </p>
   <p>
    <code class="literal">
     ASC
    </code>
    and
    <code class="literal">
     DESC
    </code>
    are supported
        for functional key parts.
   </p>
   <p>
    Functional key parts must adhere to the following rules. An
        error occurs if a key part definition contains disallowed
        constructs.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       In index definitions, enclose expressions within parentheses
            to distinguish them from columns or column prefixes. For
            example, this is permitted; the expressions are enclosed
            within parentheses:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa15996270"><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">INDEX</span> <span class="token punctuation">(</span><span class="token punctuation">(</span>col1 <span class="token operator">+</span> col2<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>col3 <span class="token operator">-</span> col4<span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       This produces an error; the expressions are not enclosed
            within parentheses:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa43022606"><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">INDEX</span> <span class="token punctuation">(</span>col1 <span class="token operator">+</span> col2<span class="token punctuation">,</span> col3 <span class="token operator">-</span> col4<span class="token punctuation">)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       A functional key part cannot consist solely of a column
            name. For example, this is not permitted:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa35149159"><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">INDEX</span> <span class="token punctuation">(</span><span class="token punctuation">(</span>col1<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>col2<span class="token punctuation">)</span><span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       Instead, write the key parts as nonfunctional key parts,
            without parentheses:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa80024135"><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">INDEX</span> <span class="token punctuation">(</span>col1<span class="token punctuation">,</span> col2<span class="token punctuation">)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       A functional key part expression cannot refer to column
            prefixes. For a workaround, see the discussion of
       <a class="link" href="string-functions.html#function_substring">
        <code class="literal">
         SUBSTRING()
        </code>
       </a>
       and
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST()
        </code>
       </a>
       later in this section.
      </p>
     </li>
     <li class="listitem">
      <p>
       Functional key parts are not permitted in foreign key
            specifications.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For
    <a class="link" href="create-table-like.html" title="15.1.20.3 CREATE TABLE ... LIKE Statement">
     <code class="literal">
      CREATE
        TABLE ... LIKE
     </code>
    </a>
    , the destination table preserves
        functional key parts from the original table.
   </p>
   <p>
    Functional indexes are implemented as hidden virtual generated
        columns, which has these implications:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Each functional key part counts against the limit on total
            number of table columns; see
       <a class="xref" href="column-count-limit.html" title="10.4.7 Limits on Table Column Count and Row Size">
        Section 10.4.7, “Limits on Table Column Count and Row Size”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Functional key parts inherit all restrictions that apply to
            generated columns. Examples:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          Only functions permitted for generated columns are
                permitted for functional key parts.
         </p>
        </li>
        <li class="listitem">
         <p>
          Subqueries, parameters, variables, stored functions, and
                loadable functions are not permitted.
         </p>
        </li>
       </ul>
      </div>
      <p>
       For more information about applicable restrictions, see
       <a class="xref" href="create-table-generated-columns.html" title="15.1.20.8 CREATE TABLE and Generated Columns">
        Section 15.1.20.8, “CREATE TABLE and Generated Columns”
       </a>
       , and
       <a class="xref" href="alter-table-generated-columns.html" title="15.1.9.2 ALTER TABLE and Generated Columns">
        Section 15.1.9.2, “ALTER TABLE and Generated Columns”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The virtual generated column itself requires no storage. The
            index itself takes up storage space as any other index.
      </p>
     </li>
    </ul>
   </div>
   <p>
    <code class="literal">
     UNIQUE
    </code>
    is supported for indexes that include
        functional key parts. However, primary keys cannot include
        functional key parts. A primary key requires the generated
        column to be stored, but functional key parts are implemented as
        virtual generated columns, not stored generated columns.
   </p>
   <p>
    <code class="literal">
     SPATIAL
    </code>
    and
    <code class="literal">
     FULLTEXT
    </code>
    indexes cannot have functional key parts.
   </p>
   <p>
    If a table contains no primary key,
    <code class="literal">
     InnoDB
    </code>
    automatically promotes the first
    <code class="literal">
     UNIQUE NOT
        NULL
    </code>
    index to the primary key. This is not supported
        for
    <code class="literal">
     UNIQUE NOT NULL
    </code>
    indexes that have
        functional key parts.
   </p>
   <p>
    Nonfunctional indexes raise a warning if there are duplicate
        indexes. Indexes that contain functional key parts do not have
        this feature.
   </p>
   <p>
    To remove a column that is referenced by a functional key part,
        the index must be removed first. Otherwise, an error occurs.
   </p>
   <p>
    Although nonfunctional key parts support a prefix length
        specification, this is not possible for functional key parts.
        The solution is to use
    <a class="link" href="string-functions.html#function_substring">
     <code class="literal">
      SUBSTRING()
     </code>
    </a>
    (or
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    , as described later in
        this section). For a functional key part containing the
    <a class="link" href="string-functions.html#function_substring">
     <code class="literal">
      SUBSTRING()
     </code>
    </a>
    function to be used
        in a query, the
    <code class="literal">
     WHERE
    </code>
    clause must contain
    <a class="link" href="string-functions.html#function_substring">
     <code class="literal">
      SUBSTRING()
     </code>
    </a>
    with the same
        arguments. In the following example, only the second
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    is able to use the index
        because that is the only query in which the arguments to
    <a class="link" href="string-functions.html#function_substring">
     <code class="literal">
      SUBSTRING()
     </code>
    </a>
    match the index
        specification:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1449497"><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> tbl <span class="token punctuation">(</span>
  col1 <span class="token datatype">LONGTEXT</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> idx1 <span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token function">SUBSTRING</span><span class="token punctuation">(</span>col1<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tbl <span class="token keyword">WHERE</span> <span class="token function">SUBSTRING</span><span class="token punctuation">(</span>col1<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">9</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">'123456789'</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tbl <span class="token keyword">WHERE</span> <span class="token function">SUBSTRING</span><span class="token punctuation">(</span>col1<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">'1234567890'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Functional key parts enable indexing of values that cannot be
        indexed otherwise, such as
    <a class="link" href="json.html" title="13.5 The JSON Data Type">
     <code class="literal">
      JSON
     </code>
    </a>
    values. However, this must be done correctly to achieve the
        desired effect. For example, this syntax does not work:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5338321"><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> employees <span class="token punctuation">(</span>
  <span class="token keyword">data</span> <span class="token datatype">JSON</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> <span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</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 punctuation">;</span></code></pre>
   </div>
   <p>
    The syntax fails because:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <a class="link" href="json-search-functions.html#operator_json-inline-path">
        <code class="literal">
         -&gt;&gt;
        </code>
       </a>
       operator translates into
       <a class="link" href="json-modification-functions.html#function_json-unquote">
        <code class="literal">
         JSON_UNQUOTE(JSON_EXTRACT(...))
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="json-modification-functions.html#function_json-unquote">
        <code class="literal">
         JSON_UNQUOTE()
        </code>
       </a>
       returns a
            value with a data type of
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         LONGTEXT
        </code>
       </a>
       , and the hidden
            generated column thus is assigned the same data type.
      </p>
     </li>
     <li class="listitem">
      <p>
       MySQL cannot index
       <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
        <code class="literal">
         LONGTEXT
        </code>
       </a>
       columns specified without a prefix length on the key part,
            and prefix lengths are not permitted in functional key
            parts.
      </p>
     </li>
    </ul>
   </div>
   <p>
    To index the
    <code class="literal">
     JSON
    </code>
    column, you could try using
        the
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    function as follows:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36636309"><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> employees <span class="token punctuation">(</span>
  <span class="token keyword">data</span> <span class="token datatype">JSON</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> <span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span><span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$.name'</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The hidden generated column is assigned the
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      VARCHAR(30)
     </code>
    </a>
    data type, which can
        be indexed. But this approach produces a new issue when trying
        to use the index:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST()
        </code>
       </a>
       returns a string with
            the collation
       <code class="literal">
        utf8mb4_0900_ai_ci
       </code>
       (the
            server default collation).
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="json-modification-functions.html#function_json-unquote">
        <code class="literal">
         JSON_UNQUOTE()
        </code>
       </a>
       returns a
            string with the collation
       <code class="literal">
        utf8mb4_bin
       </code>
       (hard coded).
      </p>
     </li>
    </ul>
   </div>
   <p>
    As a result, there is a collation mismatch between the indexed
        expression in the preceding table definition and the
    <code class="literal">
     WHERE
    </code>
    clause expression in the following
        query:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa19802754"><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> employees <span class="token keyword">WHERE</span> <span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$.name'</span> <span class="token operator">=</span> <span class="token string">'James'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The index is not used because the expressions in the query and
        the index differ. To support this kind of scenario for
        functional key parts, the optimizer automatically strips
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    when looking for an index
        to use, but
    <span class="emphasis">
     <em>
      only
     </em>
    </span>
    if the collation of the
        indexed expression matches that of the query expression. For an
        index with a functional key part to be used, either of the
        following two solutions work (although they differ somewhat in
        effect):
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Solution 1. Assign the indexed expression the same collation
            as
       <a class="link" href="json-modification-functions.html#function_json-unquote">
        <code class="literal">
         JSON_UNQUOTE()
        </code>
       </a>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36577371"><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> employees <span class="token punctuation">(</span>
  <span class="token keyword">data</span> <span class="token datatype">JSON</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> idx <span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span><span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">"$.name"</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees <span class="token keyword">VALUES</span>
  <span class="token punctuation">(</span><span class="token string">'{ "name": "james", "salary": 9000 }'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token string">'{ "name": "James", "salary": 10000 }'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token string">'{ "name": "Mary", "salary": 12000 }'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token string">'{ "name": "Peter", "salary": 8000 }'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> <span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$.name'</span> <span class="token operator">=</span> <span class="token string">'James'</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The
       <code class="literal">
        -&gt;&gt;
       </code>
       operator is the same as
       <code class="literal">
        JSON_UNQUOTE(JSON_EXTRACT(...))
       </code>
       , and
       <code class="literal">
        JSON_UNQUOTE()
       </code>
       returns a string with
            collation
       <code class="literal">
        utf8mb4_bin
       </code>
       . The comparison is
            thus case-sensitive, and only one row matches:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53917550"><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 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> data                               <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> {"name": "James", "salary": 10000} <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>
     </li>
     <li class="listitem">
      <p>
       Solution 2. Specify the full expression in the query:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76485731"><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> employees <span class="token punctuation">(</span>
  <span class="token keyword">data</span> <span class="token datatype">JSON</span><span class="token punctuation">,</span>
  <span class="token keyword">INDEX</span> idx <span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span><span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">"$.name"</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">)</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">INSERT</span> <span class="token keyword">INTO</span> employees <span class="token keyword">VALUES</span>
  <span class="token punctuation">(</span><span class="token string">'{ "name": "james", "salary": 9000 }'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token string">'{ "name": "James", "salary": 10000 }'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token string">'{ "name": "Mary", "salary": 12000 }'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token punctuation">(</span><span class="token string">'{ "name": "Peter", "salary": 8000 }'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$.name'</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token string">'James'</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       <code class="literal">
        CAST()
       </code>
       returns a string with collation
       <code class="literal">
        utf8mb4_0900_ai_ci
       </code>
       , so the comparison
            case-insensitive and two rows match:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49747148"><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 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> data                               <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> {"name": "james", "salary": 9000}  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> {"name": "James", "salary": 10000} <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>
     </li>
    </ul>
   </div>
   <p>
    Be aware that although the optimizer supports automatically
        stripping
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    with indexed
        generated columns, the following approach does not work because
        it produces a different result with and without an index
        (Bug#27337092):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72903372"><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> employees <span class="token punctuation">(</span>
         <span class="token keyword">data</span> <span class="token datatype">JSON</span><span class="token punctuation">,</span>
         generated_col <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span><span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$.name'</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
       <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected, 1 warning (0.03 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees <span class="token punctuation">(</span><span class="token keyword">data</span><span class="token punctuation">)</span>
       <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'{"name": "james"}'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token string">'{"name": "James"}'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 2 rows affected, 1 warning (0.01 sec)</span>
<span class="token output">Records: 2  Duplicates: 0  Warnings: 1</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> <span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$.name'</span> <span class="token operator">=</span> <span class="token string">'James'</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>
<span class="token output"><span class="token punctuation">|</span> data              <span class="token punctuation">|</span> generated_col <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>
<span class="token output"><span class="token punctuation">|</span> {"name": "James"} <span class="token punctuation">|</span> James         <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>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> employees <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> idx <span class="token punctuation">(</span>generated_col<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected, 1 warning (0.03 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 1</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees <span class="token keyword">WHERE</span> <span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$.name'</span> <span class="token operator">=</span> <span class="token string">'James'</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>
<span class="token output"><span class="token punctuation">|</span> data              <span class="token punctuation">|</span> generated_col <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>
<span class="token output"><span class="token punctuation">|</span> {"name": "james"} <span class="token punctuation">|</span> james         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> {"name": "James"} <span class="token punctuation">|</span> James         <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>
<span class="token output">2 rows in set (0.01 sec)</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="create-index-unique">
       </a>
       Unique Indexes
      </h4>
     </div>
    </div>
   </div>
   <p>
    A
    <code class="literal">
     UNIQUE
    </code>
    index creates a constraint such that
        all values in the index must be distinct. An error occurs if you
        try to add a new row with a key value that matches an existing
        row. If you specify a prefix value for a column in a
    <code class="literal">
     UNIQUE
    </code>
    index, the column values must be
        unique within the prefix length. A
    <code class="literal">
     UNIQUE
    </code>
    index permits multiple
    <code class="literal">
     NULL
    </code>
    values for
        columns that can contain
    <code class="literal">
     NULL
    </code>
    .
   </p>
   <a class="indexterm" name="idm46045187870480">
   </a>
   <p>
    If a table has a
    <code class="literal">
     PRIMARY KEY
    </code>
    or
    <code class="literal">
     UNIQUE NOT NULL
    </code>
    index that consists of a
        single column that has an integer type, you can use
    <code class="literal">
     _rowid
    </code>
    to refer to the indexed column in
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statements, as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        _rowid
       </code>
       refers to the
       <code class="literal">
        PRIMARY
            KEY
       </code>
       column if there is a
       <code class="literal">
        PRIMARY
            KEY
       </code>
       consisting of a single integer column. If
            there is a
       <code class="literal">
        PRIMARY KEY
       </code>
       but it does not
            consist of a single integer column,
       <code class="literal">
        _rowid
       </code>
       cannot be used.
      </p>
     </li>
     <li class="listitem">
      <p>
       Otherwise,
       <code class="literal">
        _rowid
       </code>
       refers to the column in
            the first
       <code class="literal">
        UNIQUE NOT NULL
       </code>
       index if that
            index consists of a single integer column. If the first
       <code class="literal">
        UNIQUE NOT NULL
       </code>
       index does not consist of
            a single integer column,
       <code class="literal">
        _rowid
       </code>
       cannot be
            used.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="create-index-fulltext">
       </a>
       Full-Text Indexes
      </h4>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     FULLTEXT
    </code>
    indexes are supported only for
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    and
    <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
     <code class="literal">
      MyISAM
     </code>
    </a>
    tables and can include only
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      CHAR
     </code>
    </a>
    ,
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      VARCHAR
     </code>
    </a>
    , and
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      TEXT
     </code>
    </a>
    columns. Indexing always
        happens over the entire column; column prefix indexing is not
        supported and any prefix length is ignored if specified. See
    <a class="xref" href="fulltext-search.html" title="14.9 Full-Text Search Functions">
     Section 14.9, “Full-Text Search Functions”
    </a>
    , for details of operation.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="create-index-multi-valued">
       </a>
       Multi-Valued Indexes
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045187845584">
   </a>
   <p>
    <code class="literal">
     InnoDB
    </code>
    supports multi-valued indexes. A
        multi-valued index is a secondary index defined on a column that
        stores an array of values. A
    <span class="quote">
     “
     <span class="quote">
      normal
     </span>
     ”
    </span>
    index has one
        index record for each data record (1:1). A multi-valued index
        can have multiple index records for a single data record (N:1).
        Multi-valued indexes are intended for indexing
    <code class="literal">
     JSON
    </code>
    arrays. For example, a multi-valued
        index defined on the array of zip codes in the following JSON
        document creates an index record for each zip code, with each
        index record referencing the same data record.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-json"><div class="docs-select-all right" id="sa24466944"><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-json"><span class="token punctuation">{</span>
    <span class="token property">"user"</span><span class="token operator">:</span><span class="token string">"Bob"</span><span class="token punctuation">,</span>
    <span class="token property">"user_id"</span><span class="token operator">:</span><span class="token number">31</span><span class="token punctuation">,</span>
    <span class="token property">"zipcode"</span><span class="token operator">:</span><span class="token punctuation">[</span><span class="token number">94477</span><span class="token punctuation">,</span><span class="token number">94536</span><span class="token punctuation">]</span>
<span class="token punctuation">}</span></code></pre>
   </div>
   <h5>
    <a name="idm46045187840672">
    </a>
    Creating multi-valued Indexes
   </h5>
   <p>
    You can create a multi-valued index in a
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    ,
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    , or
    <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
     <code class="literal">
      CREATE INDEX
     </code>
    </a>
    statement. This
        requires using
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST(... AS ...
        ARRAY)
     </code>
    </a>
    in the index definition, which casts same-typed
        scalar values in a
    <code class="literal">
     JSON
    </code>
    array to an SQL data
        type array. A virtual column is then generated transparently
        with the values in the SQL data type array; finally, a
        functional index (also referred to as a virtual index) is
        created on the virtual column. It is the functional index
        defined on the virtual column of values from the SQL data type
        array that forms the multi-valued index.
   </p>
   <p>
    The examples in the following list show the three different ways
        in which a multi-valued index
    <code class="literal">
     zips
    </code>
    can be
        created on an array
    <code class="literal">
     $.zipcode
    </code>
    on a
    <code class="literal">
     JSON
    </code>
    column
    <code class="literal">
     custinfo
    </code>
    in a
        table named
    <code class="literal">
     customers
    </code>
    . In each case, the JSON
        array is cast to an SQL data type array of
    <code class="literal">
     UNSIGNED
    </code>
    integer values.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        CREATE TABLE
       </code>
       only:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa19638559"><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> customers <span class="token punctuation">(</span>
    id <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    modified <span class="token datatype">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
    custinfo <span class="token datatype">JSON</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> zips<span class="token punctuation">(</span> <span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">ARRAY</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">)</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        CREATE TABLE
       </code>
       plus
       <code class="literal">
        ALTER
            TABLE
       </code>
       :
      </p>
      <p>
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa23511460"><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> customers <span class="token punctuation">(</span>
    id <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    modified <span class="token datatype">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
    custinfo <span class="token datatype">JSON</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> customers <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> zips<span class="token punctuation">(</span> <span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">ARRAY</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        CREATE TABLE
       </code>
       plus
       <code class="literal">
        CREATE
            INDEX
       </code>
       :
      </p>
      <p>
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa73932871"><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> customers <span class="token punctuation">(</span>
    id <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    modified <span class="token datatype">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
    custinfo <span class="token datatype">JSON</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> zips <span class="token keyword">ON</span> customers <span class="token punctuation">(</span> <span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">ARRAY</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
      </p>
     </li>
    </ul>
   </div>
   <p>
    A multi-valued index can also be defined as part of a composite
        index. This example shows a composite index that includes two
        single-valued parts (for the
    <code class="literal">
     id
    </code>
    and
    <code class="literal">
     modified
    </code>
    columns), and one multi-valued part
        (for the
    <code class="literal">
     custinfo
    </code>
    column):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa74682398"><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> customers <span class="token punctuation">(</span>
    id <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    modified <span class="token datatype">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
    custinfo <span class="token datatype">JSON</span>
    <span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> customers <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> comp<span class="token punctuation">(</span>id<span class="token punctuation">,</span> modified<span class="token punctuation">,</span>
    <span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">ARRAY</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Only one multi-valued key part can be used in a composite index.
        The multi-valued key part may be used in any order relative to
        the other parts of the key. In other words, the
    <code class="literal">
     ALTER
        TABLE
    </code>
    statement just shown could have used
    <code class="literal">
     comp(id, (CAST(custinfo-&gt;'$.zipcode' AS UNSIGNED
        ARRAY), modified))
    </code>
    (or any other ordering) and still
        have been valid.
   </p>
   <h5>
    <a name="idm46045187810832">
    </a>
    Using multi-valued Indexes
   </h5>
   <p>
    The optimizer uses a multi-valued index to fetch records when
        the following functions are specified in a
    <code class="literal">
     WHERE
    </code>
    clause:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="json-search-functions.html#operator_member-of">
        <code class="literal">
         MEMBER OF()
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="json-search-functions.html#function_json-contains">
        <code class="literal">
         JSON_CONTAINS()
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="json-search-functions.html#function_json-overlaps">
        <code class="literal">
         JSON_OVERLAPS()
        </code>
       </a>
      </p>
     </li>
    </ul>
   </div>
   <p>
    We can demonstrate this by creating and populating the
    <code class="literal">
     customers
    </code>
    table using the following
    <code class="literal">
     CREATE TABLE
    </code>
    and
    <code class="literal">
     INSERT
    </code>
    statements:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96678191"><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> customers <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     modified <span class="token datatype">DATETIME</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     custinfo <span class="token datatype">JSON</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.51 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> customers <span class="token keyword">VALUES</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'{"user":"Mary","user_id":72,"zipcode":[94536]}'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token function">NOW</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'{"user":"Ted","user_id":56,"zipcode":[94507,94582]}'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 5 rows affected (0.07 sec)</span>
<span class="token output">Records: 5  Duplicates: 0  Warnings: 0</span></code></pre>
   </div>
   <p>
    First we execute three queries on the
    <code class="literal">
     customers
    </code>
    table, one each using
    <code class="literal">
     MEMBER OF()
    </code>
    ,
    <code class="literal">
     JSON_CONTAINS()
    </code>
    , and
    <code class="literal">
     JSON_OVERLAPS()
    </code>
    , with the result from each
        query shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58414434"><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> <span class="token operator">*</span> <span class="token keyword">FROM</span> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token number">94507</span> <span class="token function">MEMBER OF</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> id <span class="token punctuation">|</span> modified            <span class="token punctuation">|</span> custinfo                                                          <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>  2 <span class="token punctuation">|</span> 2019<span class="token punctuation">-</span>06<span class="token punctuation">-</span>29 22:23:12 <span class="token punctuation">|</span> {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <span class="token punctuation">|</span> 2019<span class="token punctuation">-</span>06<span class="token punctuation">-</span>29 22:23:12 <span class="token punctuation">|</span> {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span> 2019<span class="token punctuation">-</span>06<span class="token punctuation">-</span>29 22:23:12 <span class="token punctuation">|</span> {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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">3 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token function">JSON_CONTAINS</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'[94507,94582]'</span> <span class="token keyword">AS</span> <span class="token datatype">JSON</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> id <span class="token punctuation">|</span> modified            <span class="token punctuation">|</span> custinfo                                                          <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>  2 <span class="token punctuation">|</span> 2019<span class="token punctuation">-</span>06<span class="token punctuation">-</span>29 22:23:12 <span class="token punctuation">|</span> {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span> 2019<span class="token punctuation">-</span>06<span class="token punctuation">-</span>29 22:23:12 <span class="token punctuation">|</span> {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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">2 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token function">JSON_OVERLAPS</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'[94507,94582]'</span> <span class="token keyword">AS</span> <span class="token datatype">JSON</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> id <span class="token punctuation">|</span> modified            <span class="token punctuation">|</span> custinfo                                                          <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> 2019<span class="token punctuation">-</span>06<span class="token punctuation">-</span>29 22:23:12 <span class="token punctuation">|</span> {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span> 2019<span class="token punctuation">-</span>06<span class="token punctuation">-</span>29 22:23:12 <span class="token punctuation">|</span> {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <span class="token punctuation">|</span> 2019<span class="token punctuation">-</span>06<span class="token punctuation">-</span>29 22:23:12 <span class="token punctuation">|</span> {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span> 2019<span class="token punctuation">-</span>06<span class="token punctuation">-</span>29 22:23:12 <span class="token punctuation">|</span> {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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">4 rows in set (0.00 sec)</span></code></pre>
   </div>
   <p>
    Next, we run
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    on each of
        the previous three queries:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92377467"><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> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token number">94507</span> <span class="token function">MEMBER OF</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> id <span class="token punctuation">|</span> select_type <span class="token punctuation">|</span> table     <span class="token punctuation">|</span> partitions <span class="token punctuation">|</span> type <span class="token punctuation">|</span> possible_keys <span class="token punctuation">|</span> key  <span class="token punctuation">|</span> key_len <span class="token punctuation">|</span> ref  <span class="token punctuation">|</span> rows <span class="token punctuation">|</span> filtered <span class="token punctuation">|</span> Extra       <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 class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> SIMPLE      <span class="token punctuation">|</span> customers <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span> ALL  <span class="token punctuation">|</span> NULL          <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>   100.00 <span class="token punctuation">|</span> Using where <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 class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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">1 row in set, 1 warning (0.00 sec)</span>

<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> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token function">JSON_CONTAINS</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'[94507,94582]'</span> <span class="token keyword">AS</span> <span class="token datatype">JSON</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> id <span class="token punctuation">|</span> select_type <span class="token punctuation">|</span> table     <span class="token punctuation">|</span> partitions <span class="token punctuation">|</span> type <span class="token punctuation">|</span> possible_keys <span class="token punctuation">|</span> key  <span class="token punctuation">|</span> key_len <span class="token punctuation">|</span> ref  <span class="token punctuation">|</span> rows <span class="token punctuation">|</span> filtered <span class="token punctuation">|</span> Extra       <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 class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> SIMPLE      <span class="token punctuation">|</span> customers <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span> ALL  <span class="token punctuation">|</span> NULL          <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>   100.00 <span class="token punctuation">|</span> Using where <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 class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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">1 row in set, 1 warning (0.00 sec)</span>

<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> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token function">JSON_OVERLAPS</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'[94507,94582]'</span> <span class="token keyword">AS</span> <span class="token datatype">JSON</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> id <span class="token punctuation">|</span> select_type <span class="token punctuation">|</span> table     <span class="token punctuation">|</span> partitions <span class="token punctuation">|</span> type <span class="token punctuation">|</span> possible_keys <span class="token punctuation">|</span> key  <span class="token punctuation">|</span> key_len <span class="token punctuation">|</span> ref  <span class="token punctuation">|</span> rows <span class="token punctuation">|</span> filtered <span class="token punctuation">|</span> Extra       <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 class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> SIMPLE      <span class="token punctuation">|</span> customers <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span> ALL  <span class="token punctuation">|</span> NULL          <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>    5 <span class="token punctuation">|</span>   100.00 <span class="token punctuation">|</span> Using where <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 class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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">1 row in set, 1 warning (0.01 sec)</span></code></pre>
   </div>
   <p>
    None of the three queries just shown are able to use any keys.
        To solve this problem, we can add a multi-valued index on the
    <code class="literal">
     zipcode
    </code>
    array in the
    <code class="literal">
     JSON
    </code>
    column (
    <code class="literal">
     custinfo
    </code>
    ), like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81987568"><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">ALTER</span> <span class="token keyword">TABLE</span> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> zips<span class="token punctuation">(</span> <span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">ARRAY</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.47 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span></code></pre>
   </div>
   <p>
    When we run the previous
    <code class="literal">
     EXPLAIN
    </code>
    statements
        again, we can now observe that the queries can (and do) use the
        index
    <code class="literal">
     zips
    </code>
    that was just created:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34930699"><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> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token number">94507</span> <span class="token function">MEMBER OF</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> id <span class="token punctuation">|</span> select_type <span class="token punctuation">|</span> table     <span class="token punctuation">|</span> partitions <span class="token punctuation">|</span> type <span class="token punctuation">|</span> possible_keys <span class="token punctuation">|</span> key  <span class="token punctuation">|</span> key_len <span class="token punctuation">|</span> ref   <span class="token punctuation">|</span> rows <span class="token punctuation">|</span> filtered <span class="token punctuation">|</span> Extra       <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 class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> SIMPLE      <span class="token punctuation">|</span> customers <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span> ref  <span class="token punctuation">|</span> zips          <span class="token punctuation">|</span> zips <span class="token punctuation">|</span> 9       <span class="token punctuation">|</span> const <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>   100.00 <span class="token punctuation">|</span> Using where <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 class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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">1 row in set, 1 warning (0.00 sec)</span>

<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> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token function">JSON_CONTAINS</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'[94507,94582]'</span> <span class="token keyword">AS</span> <span class="token datatype">JSON</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> id <span class="token punctuation">|</span> select_type <span class="token punctuation">|</span> table     <span class="token punctuation">|</span> partitions <span class="token punctuation">|</span> type  <span class="token punctuation">|</span> possible_keys <span class="token punctuation">|</span> key  <span class="token punctuation">|</span> key_len <span class="token punctuation">|</span> ref  <span class="token punctuation">|</span> rows <span class="token punctuation">|</span> filtered <span class="token punctuation">|</span> Extra       <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> SIMPLE      <span class="token punctuation">|</span> customers <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span> range <span class="token punctuation">|</span> zips          <span class="token punctuation">|</span> zips <span class="token punctuation">|</span> 9       <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>    6 <span class="token punctuation">|</span>   100.00 <span class="token punctuation">|</span> Using where <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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">1 row in set, 1 warning (0.00 sec)</span>

<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> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token function">JSON_OVERLAPS</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'[94507,94582]'</span> <span class="token keyword">AS</span> <span class="token datatype">JSON</span><span class="token punctuation">)</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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> id <span class="token punctuation">|</span> select_type <span class="token punctuation">|</span> table     <span class="token punctuation">|</span> partitions <span class="token punctuation">|</span> type  <span class="token punctuation">|</span> possible_keys <span class="token punctuation">|</span> key  <span class="token punctuation">|</span> key_len <span class="token punctuation">|</span> ref  <span class="token punctuation">|</span> rows <span class="token punctuation">|</span> filtered <span class="token punctuation">|</span> Extra       <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> SIMPLE      <span class="token punctuation">|</span> customers <span class="token punctuation">|</span> NULL       <span class="token punctuation">|</span> range <span class="token punctuation">|</span> zips          <span class="token punctuation">|</span> zips <span class="token punctuation">|</span> 9       <span class="token punctuation">|</span> NULL <span class="token punctuation">|</span>    6 <span class="token punctuation">|</span>   100.00 <span class="token punctuation">|</span> Using where <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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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">1 row in set, 1 warning (0.01 sec)</span></code></pre>
   </div>
   <p>
    A multi-valued index can be defined as a unique key. If defined
        as a unique key, attempting to insert a value already present in
        the multi-valued index returns a duplicate key error. If
        duplicate values are already present, attempting to add a unique
        multi-valued index fails, as shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14618238"><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">ALTER</span> <span class="token keyword">TABLE</span> customers <span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> zips<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.55 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">ADD</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> zips<span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">ARRAY</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1062 (23000)<span class="token punctuation">:</span> Duplicate entry '[94507, ' for key 'customers.zips'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> customers
<span class="token prompt">    -&gt;</span>     <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> zips<span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token function">CAST</span><span class="token punctuation">(</span>custinfo<span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.zipcode'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">ARRAY</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.36 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span></code></pre>
   </div>
   <h5>
    <a name="idm46045187745664">
    </a>
    Characteristics of Multi-Valued Indexes
   </h5>
   <p>
    Multi-valued indexes have the additional characteristics listed
        here:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       DML operations that affect multi-valued indexes are handled
            in the same way as DML operations that affect a normal
            index, with the only difference being that there may be more
            than one insert or update for a single clustered index
            record.
      </p>
     </li>
     <li class="listitem">
      <p>
       Nullability and multi-valued indexes:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If a multi-valued key part has an empty array, no
                entries are added to the index, and the data record is
                not accessible by an index scan.
         </p>
        </li>
        <li class="listitem">
         <p>
          If multi-valued key part generation returns a
          <code class="literal">
           NULL
          </code>
          value, a single entry containing
          <code class="literal">
           NULL
          </code>
          is added to the multi-valued
                index. If the key part is defined as
          <code class="literal">
           NOT
                NULL
          </code>
          , an error is reported.
         </p>
        </li>
        <li class="listitem">
         <p>
          If the typed array column is set to
          <code class="literal">
           NULL
          </code>
          , the storage engine stores a
                single record containing
          <code class="literal">
           NULL
          </code>
          that
                points to the data record.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           JSON
          </code>
          null values are not permitted in
                indexed arrays. If any returned value is
          <code class="literal">
           NULL
          </code>
          , it is treated as a JSON null
                and an
          <span class="errortext">
           Invalid JSON value
          </span>
          error
                is reported.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       Because multi-valued indexes are virtual indexes on virtual
            columns, they must adhere to the same rules as secondary
            indexes on virtual generated columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       Index records are not added for empty arrays.
      </p>
     </li>
    </ul>
   </div>
   <h5>
    <a name="idm46045187729664">
    </a>
    Limitations and Restrictions on Multi-valued Indexes
   </h5>
   <p>
    Multi-valued indexes are subject to the limitations and
        restrictions listed here:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Only one multi-valued key part is permitted per multi-valued
            index. However, the
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST(... AS ...
            ARRAY)
        </code>
       </a>
       expression can refer to multiple arrays
            within a
       <code class="literal">
        JSON
       </code>
       document, as shown here:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa99727756"><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 function">CAST</span><span class="token punctuation">(</span><span class="token keyword">data</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token string">'$.arr[*][*]'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">ARRAY</span><span class="token punctuation">)</span></code></pre>
      </div>
      <p>
       In this case, all values matching the JSON expression are
            stored in the index as a single flat array.
      </p>
     </li>
     <li class="listitem">
      <p>
       An index with a multi-valued key part does not support
            ordering and therefore cannot be used as a primary key. For
            the same reason, a multi-valued index cannot be defined
            using the
       <code class="literal">
        ASC
       </code>
       or
       <code class="literal">
        DESC
       </code>
       keyword.
      </p>
     </li>
     <li class="listitem">
      <p>
       A multi-valued index cannot be a covering index.
      </p>
     </li>
     <li class="listitem">
      <p>
       The maximum number of values per record for a multi-valued
            index is determined by the amount of data than can be stored
            on a single undo log page, which is 65221 bytes (64K minus
            315 bytes for overhead), which means that the maximum total
            length of key values is also 65221 bytes. The maximum number
            of keys depends on various factors, which prevents defining
            a specific limit. Tests have shown a multi-valued index to
            permit as many as 1604 integer keys per record, for example.
            When the limit is reached, an error similar to the following
            is reported:
       <span class="errortext">
        ERROR 3905 (HY000): Exceeded max
            number of values per record for multi-valued index 'idx' by
            1 value(s).
       </span>
      </p>
     </li>
     <li class="listitem">
      <p>
       The only type of expression that is permitted in a
            multi-valued key part is a
       <code class="literal">
        JSON
       </code>
       expression. The expression need not reference an existing
            element in a JSON document inserted into the indexed column,
            but must itself be syntactically valid.
      </p>
     </li>
     <li class="listitem">
      <p>
       Because index records for the same clustered index record
            are dispersed throughout a multi-valued index, a
            multi-valued index does not support range scans or
            index-only scans.
      </p>
     </li>
     <li class="listitem">
      <p>
       Multi-valued indexes are not permitted in foreign key
            specifications.
      </p>
     </li>
     <li class="listitem">
      <p>
       Index prefixes cannot be defined for multi-valued indexes.
      </p>
     </li>
     <li class="listitem">
      <p>
       Multi-valued indexes cannot be defined on data cast as
       <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
        <code class="literal">
         BINARY
        </code>
       </a>
       (see the description
            of the
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST()
        </code>
       </a>
       function).
      </p>
     </li>
     <li class="listitem">
      <p>
       Online creation of a multi-value index is not supported,
            which means the operation uses
       <code class="literal">
        ALGORITHM=COPY
       </code>
       . See
       <a class="xref" href="alter-table.html#alter-table-performance" title="Performance and Space Requirements">
        Performance and Space Requirements
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Character sets and collations other than the following two
            combinations of character set and collation are not
            supported for multi-valued indexes:
      </p>
      <div class="orderedlist">
       <ol class="orderedlist" type="1">
        <li class="listitem">
         <p>
          The
          <code class="literal">
           binary
          </code>
          character set with the
                default
          <code class="literal">
           binary
          </code>
          collation
         </p>
        </li>
        <li class="listitem">
         <p>
          The
          <code class="literal">
           utf8mb4
          </code>
          character set with the
                default
          <code class="literal">
           utf8mb4_0900_as_cs
          </code>
          collation.
         </p>
        </li>
       </ol>
      </div>
     </li>
     <li class="listitem">
      <p>
       As with other indexes on columns of
       <code class="literal">
        InnoDB
       </code>
       tables, a multi-valued index
            cannot be created with
       <code class="literal">
        USING HASH
       </code>
       ;
            attempting to do so results in a warning:
       <span class="errortext">
        This
            storage engine does not support the HASH index algorithm,
            storage engine default was used instead
       </span>
       .
            (
       <code class="literal">
        USING BTREE
       </code>
       is supported as usual.)
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="create-index-spatial">
       </a>
       Spatial Indexes
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
     <code class="literal">
      MyISAM
     </code>
    </a>
    ,
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    ,
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDB
     </code>
    </a>
    , and
    <a class="link" href="archive-storage-engine.html" title="18.5 The ARCHIVE Storage Engine">
     <code class="literal">
      ARCHIVE
     </code>
    </a>
    storage engines support
        spatial columns such as
    <a class="link" href="spatial-type-overview.html" title="13.4.1 Spatial Data Types">
     <code class="literal">
      POINT
     </code>
    </a>
    and
    <a class="link" href="spatial-type-overview.html" title="13.4.1 Spatial Data Types">
     <code class="literal">
      GEOMETRY
     </code>
    </a>
    .
        (
    <a class="xref" href="spatial-types.html" title="13.4 Spatial Data Types">
     Section 13.4, “Spatial Data Types”
    </a>
    , describes the spatial data
        types.) However, support for spatial column indexing varies
        among engines. Spatial and nonspatial indexes on spatial columns
        are available according to the following rules.
   </p>
   <p>
    Spatial indexes on spatial columns have these characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Available only for
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       and
       <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
        <code class="literal">
         MyISAM
        </code>
       </a>
       tables. Specifying
       <code class="literal">
        SPATIAL INDEX
       </code>
       for other storage engines
            results in an error.
      </p>
     </li>
     <li class="listitem">
      <p>
       An index on a spatial column
       <span class="emphasis">
        <em>
         must
        </em>
       </span>
       be a
       <code class="literal">
        SPATIAL
       </code>
       index. The
       <code class="literal">
        SPATIAL
       </code>
       keyword is thus optional but
            implicit for creating an index on a spatial column.
      </p>
     </li>
     <li class="listitem">
      <p>
       Available for single spatial columns only. A spatial index
            cannot be created over multiple spatial columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       Indexed columns must be
       <code class="literal">
        NOT NULL
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Column prefix lengths are prohibited. The full width of each
            column is indexed.
      </p>
     </li>
     <li class="listitem">
      <p>
       Not permitted for a primary key or unique index.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Nonspatial indexes on spatial columns (created with
    <code class="literal">
     INDEX
    </code>
    ,
    <code class="literal">
     UNIQUE
    </code>
    , or
    <code class="literal">
     PRIMARY KEY
    </code>
    ) have these characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Permitted for any storage engine that supports spatial
            columns except
       <a class="link" href="archive-storage-engine.html" title="18.5 The ARCHIVE Storage Engine">
        <code class="literal">
         ARCHIVE
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Columns can be
       <code class="literal">
        NULL
       </code>
       unless the index is a
            primary key.
      </p>
     </li>
     <li class="listitem">
      <p>
       The index type for a non-
       <code class="literal">
        SPATIAL
       </code>
       index
            depends on the storage engine. Currently, B-tree is used.
      </p>
     </li>
     <li class="listitem">
      <p>
       Permitted for a column that can have
       <code class="literal">
        NULL
       </code>
       values only for
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       ,
       <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
        <code class="literal">
         MyISAM
        </code>
       </a>
       , and
       <a class="link" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
        <code class="literal">
         MEMORY
        </code>
       </a>
       tables.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="create-index-options">
       </a>
       Index Options
      </h4>
     </div>
    </div>
   </div>
   <p>
    Following the key part list, index options can be given. An
    <em class="replaceable">
     <code>
      index_option
     </code>
    </em>
    value can be any of the
        following:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        KEY_BLOCK_SIZE [=]
        <em class="replaceable">
         <code>
          value
         </code>
        </em>
       </code>
      </p>
      <p>
       For
       <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
        <code class="literal">
         MyISAM
        </code>
       </a>
       tables,
       <code class="literal">
        KEY_BLOCK_SIZE
       </code>
       optionally specifies the
            size in bytes to use for index key blocks. The value is
            treated as a hint; a different size could be used if
            necessary. A
       <code class="literal">
        KEY_BLOCK_SIZE
       </code>
       value
            specified for an individual index definition overrides a
            table-level
       <code class="literal">
        KEY_BLOCK_SIZE
       </code>
       value.
      </p>
      <p>
       <code class="literal">
        KEY_BLOCK_SIZE
       </code>
       is not supported at the
            index level for
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       tables.
            See
       <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        Section 15.1.20, “CREATE TABLE Statement”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <em class="replaceable">
        <code>
         index_type
        </code>
       </em>
      </p>
      <p>
       Some storage engines permit you to specify an index type
            when creating an index. For example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47447701"><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> lookup <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">MEMORY</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> id_index <span class="token keyword">ON</span> lookup <span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">USING</span> <span class="token keyword">BTREE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       <a class="xref" href="create-index.html#create-index-storage-engine-index-types" title="Table 15.1 Index Types Per Storage Engine">
        Table 15.1, “Index Types Per Storage Engine”
       </a>
       shows the permissible index type values supported by
            different storage engines. Where multiple index types are
            listed, the first one is the default when no index type
            specifier is given. Storage engines not listed in the table
            do not support an
       <em class="replaceable">
        <code>
         index_type
        </code>
       </em>
       clause in index definitions.
      </p>
      <div class="table">
       <a name="create-index-storage-engine-index-types">
       </a>
       <p class="title">
        <b>
         Table 15.1 Index Types Per Storage Engine
        </b>
       </p>
       <div class="table-contents">
        <table summary="Permissible index types by storage engine.">
         <colgroup>
          <col style="width: 20%"/>
          <col style="width: 50%"/>
         </colgroup>
         <thead>
          <tr>
           <th>
            Storage Engine
           </th>
           <th>
            Permissible Index Types
           </th>
          </tr>
         </thead>
         <tbody>
          <tr>
           <td>
            <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
             <code class="literal">
              InnoDB
             </code>
            </a>
           </td>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
          </tr>
          <tr>
           <td>
            <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
             <code class="literal">
              MyISAM
             </code>
            </a>
           </td>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
          </tr>
          <tr>
           <td>
            <a class="link" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
             <code class="literal">
              MEMORY
             </code>
            </a>
            /
            <code class="literal">
             HEAP
            </code>
           </td>
           <td>
            <code class="literal">
             HASH
            </code>
            ,
            <code class="literal">
             BTREE
            </code>
           </td>
          </tr>
          <tr>
           <td>
            <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
             <code class="literal">
              NDB
             </code>
            </a>
           </td>
           <td>
            <code class="literal">
             HASH
            </code>
            ,
            <code class="literal">
             BTREE
            </code>
            (see note in text)
           </td>
          </tr>
         </tbody>
        </table>
       </div>
      </div>
      <br class="table-break"/>
      <p>
       The
       <em class="replaceable">
        <code>
         index_type
        </code>
       </em>
       clause cannot be
            used for
       <code class="literal">
        FULLTEXT INDEX
       </code>
       specifications.
            Full-text index implementation is storage-engine dependent.
            Spatial indexes are implemented as R-tree indexes.
      </p>
      <p>
       If you specify an index type that is not valid for a given
            storage engine, but another index type is available that the
            engine can use without affecting query results, the engine
            uses the available type. The parser recognizes
       <code class="literal">
        RTREE
       </code>
       as a type name. This is permitted
            only for
       <code class="literal">
        SPATIAL
       </code>
       indexes.
      </p>
      <p>
       <code class="literal">
        BTREE
       </code>
       indexes are implemented by the
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       storage engine as T-tree
            indexes.
      </p>
      <a class="indexterm" name="idm46045187616368">
      </a>
      <a class="indexterm" name="idm46045187614880">
      </a>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        For indexes on
        <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
         <code class="literal">
          NDB
         </code>
        </a>
        table
              columns, the
        <code class="literal">
         USING
        </code>
        option can be
              specified only for a unique index or primary key.
        <code class="literal">
         USING HASH
        </code>
        prevents the creation of an
              ordered index; otherwise, creating a unique index or
              primary key on an
        <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
         <code class="literal">
          NDB
         </code>
        </a>
        table
              automatically results in the creation of both an ordered
              index and a hash index, each of which indexes the same set
              of columns.
       </p>
       <p>
        For unique indexes that include one or more
        <code class="literal">
         NULL
        </code>
        columns of an
        <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
         <code class="literal">
          NDB
         </code>
        </a>
        table, the hash index can
              be used only to look up literal values, which means that
        <code class="literal">
         IS [NOT] NULL
        </code>
        conditions require a full
              scan of the table. One workaround is to make sure that a
              unique index using one or more
        <code class="literal">
         NULL
        </code>
        columns on such a table is always created in such a way
              that it includes the ordered index; that is, avoid
              employing
        <code class="literal">
         USING HASH
        </code>
        when creating the
              index.
       </p>
      </div>
      <p>
       If you specify an index type that is not valid for a given
            storage engine, but another index type is available that the
            engine can use without affecting query results, the engine
            uses the available type. The parser recognizes
       <code class="literal">
        RTREE
       </code>
       as a type name, but currently this
            cannot be specified for any storage engine.
      </p>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        Use of the
        <em class="replaceable">
         <code>
          index_type
         </code>
        </em>
        option
              before the
        <code class="literal">
         ON
         <em class="replaceable">
          <code>
           tbl_name
          </code>
         </em>
        </code>
        clause is
              deprecated; expect support for use of the option in this
              position to be removed in a future MySQL release. If an
        <em class="replaceable">
         <code>
          index_type
         </code>
        </em>
        option is given in
              both the earlier and later positions, the final option
              applies.
       </p>
      </div>
      <p>
       <code class="literal">
        TYPE
        <em class="replaceable">
         <code>
          type_name
         </code>
        </em>
       </code>
       is recognized as a synonym for
       <code class="literal">
        USING
        <em class="replaceable">
         <code>
          type_name
         </code>
        </em>
       </code>
       . However,
       <code class="literal">
        USING
       </code>
       is the preferred form.
      </p>
      <p>
       The following tables show index characteristics for the
            storage engines that support the
       <em class="replaceable">
        <code>
         index_type
        </code>
       </em>
       option.
      </p>
      <div class="table">
       <a name="innodb-storage-engine-index-characteristics">
       </a>
       <p class="title">
        <b>
         Table 15.2 InnoDB Storage Engine Index Characteristics
        </b>
       </p>
       <div class="table-contents">
        <table summary="Index characteristics of the InnoDB storage engine.">
         <colgroup>
          <col style="width: 15%"/>
          <col style="width: 10%"/>
          <col style="width: 15%"/>
          <col style="width: 20%"/>
          <col style="width: 20%"/>
          <col style="width: 20%"/>
         </colgroup>
         <thead>
          <tr>
           <th scope="col">
            Index Class
           </th>
           <th scope="col">
            Index Type
           </th>
           <th scope="col">
            Stores NULL VALUES
           </th>
           <th scope="col">
            Permits Multiple NULL Values
           </th>
           <th scope="col">
            IS NULL Scan Type
           </th>
           <th scope="col">
            IS NOT NULL Scan Type
           </th>
          </tr>
         </thead>
         <tbody>
          <tr>
           <th scope="row">
            Primary key
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            No
           </td>
           <td>
            No
           </td>
           <td>
            N/A
           </td>
           <td>
            N/A
           </td>
          </tr>
          <tr>
           <th scope="row">
            Unique
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            Key
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            <code class="literal">
             FULLTEXT
            </code>
           </th>
           <td>
            N/A
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Table
           </td>
           <td>
            Table
           </td>
          </tr>
          <tr>
           <th scope="row">
            <code class="literal">
             SPATIAL
            </code>
           </th>
           <td>
            N/A
           </td>
           <td>
            No
           </td>
           <td>
            No
           </td>
           <td>
            N/A
           </td>
           <td>
            N/A
           </td>
          </tr>
         </tbody>
        </table>
       </div>
      </div>
      <br class="table-break"/>
      <div class="table">
       <a name="myisam-storage-engine-index-characteristics">
       </a>
       <p class="title">
        <b>
         Table 15.3 MyISAM Storage Engine Index Characteristics
        </b>
       </p>
       <div class="table-contents">
        <table summary="Index characteristics of the MyISAM storage engine.">
         <colgroup>
          <col style="width: 15%"/>
          <col style="width: 10%"/>
          <col style="width: 15%"/>
          <col style="width: 20%"/>
          <col style="width: 20%"/>
          <col style="width: 20%"/>
         </colgroup>
         <thead>
          <tr>
           <th scope="col">
            Index Class
           </th>
           <th scope="col">
            Index Type
           </th>
           <th scope="col">
            Stores NULL VALUES
           </th>
           <th scope="col">
            Permits Multiple NULL Values
           </th>
           <th scope="col">
            IS NULL Scan Type
           </th>
           <th scope="col">
            IS NOT NULL Scan Type
           </th>
          </tr>
         </thead>
         <tbody>
          <tr>
           <th scope="row">
            Primary key
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            No
           </td>
           <td>
            No
           </td>
           <td>
            N/A
           </td>
           <td>
            N/A
           </td>
          </tr>
          <tr>
           <th scope="row">
            Unique
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            Key
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            <code class="literal">
             FULLTEXT
            </code>
           </th>
           <td>
            N/A
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Table
           </td>
           <td>
            Table
           </td>
          </tr>
          <tr>
           <th scope="row">
            <code class="literal">
             SPATIAL
            </code>
           </th>
           <td>
            N/A
           </td>
           <td>
            No
           </td>
           <td>
            No
           </td>
           <td>
            N/A
           </td>
           <td>
            N/A
           </td>
          </tr>
         </tbody>
        </table>
       </div>
      </div>
      <br class="table-break"/>
      <div class="table">
       <a name="memory-storage-engine-index-characteristics">
       </a>
       <p class="title">
        <b>
         Table 15.4 MEMORY Storage Engine Index Characteristics
        </b>
       </p>
       <div class="table-contents">
        <table summary="Index characteristics of the Memory storage engine.">
         <colgroup>
          <col style="width: 15%"/>
          <col style="width: 10%"/>
          <col style="width: 15%"/>
          <col style="width: 20%"/>
          <col style="width: 20%"/>
          <col style="width: 20%"/>
         </colgroup>
         <thead>
          <tr>
           <th scope="col">
            Index Class
           </th>
           <th scope="col">
            Index Type
           </th>
           <th scope="col">
            Stores NULL VALUES
           </th>
           <th scope="col">
            Permits Multiple NULL Values
           </th>
           <th scope="col">
            IS NULL Scan Type
           </th>
           <th scope="col">
            IS NOT NULL Scan Type
           </th>
          </tr>
         </thead>
         <tbody>
          <tr>
           <th scope="row">
            Primary key
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            No
           </td>
           <td>
            No
           </td>
           <td>
            N/A
           </td>
           <td>
            N/A
           </td>
          </tr>
          <tr>
           <th scope="row">
            Unique
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            Key
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            Primary key
           </th>
           <td>
            <code class="literal">
             HASH
            </code>
           </td>
           <td>
            No
           </td>
           <td>
            No
           </td>
           <td>
            N/A
           </td>
           <td>
            N/A
           </td>
          </tr>
          <tr>
           <th scope="row">
            Unique
           </th>
           <td>
            <code class="literal">
             HASH
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            Key
           </th>
           <td>
            <code class="literal">
             HASH
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
         </tbody>
        </table>
       </div>
      </div>
      <br class="table-break"/>
      <div class="table">
       <a name="ndb-storage-engine-index-characteristics">
       </a>
       <p class="title">
        <b>
         Table 15.5 NDB Storage Engine Index Characteristics
        </b>
       </p>
       <div class="table-contents">
        <table summary="Index characteristics of the NDB storage engine.">
         <colgroup>
          <col style="width: 15%"/>
          <col style="width: 10%"/>
          <col style="width: 15%"/>
          <col style="width: 20%"/>
          <col style="width: 20%"/>
          <col style="width: 20%"/>
         </colgroup>
         <thead>
          <tr>
           <th scope="col">
            Index Class
           </th>
           <th scope="col">
            Index Type
           </th>
           <th scope="col">
            Stores NULL VALUES
           </th>
           <th scope="col">
            Permits Multiple NULL Values
           </th>
           <th scope="col">
            IS NULL Scan Type
           </th>
           <th scope="col">
            IS NOT NULL Scan Type
           </th>
          </tr>
         </thead>
         <tbody>
          <tr>
           <th scope="row">
            Primary key
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            No
           </td>
           <td>
            No
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            Unique
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            Key
           </th>
           <td>
            <code class="literal">
             BTREE
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Index
           </td>
           <td>
            Index
           </td>
          </tr>
          <tr>
           <th scope="row">
            Primary key
           </th>
           <td>
            <code class="literal">
             HASH
            </code>
           </td>
           <td>
            No
           </td>
           <td>
            No
           </td>
           <td>
            Table (see note 1)
           </td>
           <td>
            Table (see note 1)
           </td>
          </tr>
          <tr>
           <th scope="row">
            Unique
           </th>
           <td>
            <code class="literal">
             HASH
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Table (see note 1)
           </td>
           <td>
            Table (see note 1)
           </td>
          </tr>
          <tr>
           <th scope="row">
            Key
           </th>
           <td>
            <code class="literal">
             HASH
            </code>
           </td>
           <td>
            Yes
           </td>
           <td>
            Yes
           </td>
           <td>
            Table (see note 1)
           </td>
           <td>
            Table (see note 1)
           </td>
          </tr>
         </tbody>
        </table>
       </div>
      </div>
      <br class="table-break"/>
      <p>
       Table note:
      </p>
      <p>
       1.
       <code class="literal">
        USING HASH
       </code>
       prevents creation of an
            implicit ordered index.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        WITH PARSER
        <em class="replaceable">
         <code>
          parser_name
         </code>
        </em>
       </code>
      </p>
      <p>
       This option can be used only with
       <code class="literal">
        FULLTEXT
       </code>
       indexes. It associates a parser
            plugin with the index if full-text indexing and searching
            operations need special handling.
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       and
       <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
        <code class="literal">
         MyISAM
        </code>
       </a>
       support full-text parser
            plugins. If you have a
       <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
        <code class="literal">
         MyISAM
        </code>
       </a>
       table with an associated full-text parser plugin, you can
            convert the table to
       <code class="literal">
        InnoDB
       </code>
       using
       <code class="literal">
        ALTER TABLE
       </code>
       . See
       <a class="ulink" href="/doc/extending-mysql/8.4/en/plugin-types.html#full-text-plugin-type" target="_top">
        Full-Text Parser Plugins
       </a>
       and
       <a class="ulink" href="/doc/extending-mysql/8.4/en/writing-full-text-plugins.html" target="_top">
        Writing Full-Text Parser Plugins
       </a>
       for more
            information.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        COMMENT
            '
        <em class="replaceable">
         <code>
          string
         </code>
        </em>
        '
       </code>
      </p>
      <p>
       Index definitions can include an optional comment of up to
            1024 characters.
      </p>
      <p>
       The
       <a class="link" href="index-page-merge-threshold.html" title="17.8.11 Configuring the Merge Threshold for Index Pages">
        <code class="literal">
         MERGE_THRESHOLD
        </code>
       </a>
       for index pages can be configured for individual indexes
            using the
       <em class="replaceable">
        <code>
         index_option
        </code>
       </em>
       <code class="literal">
        COMMENT
       </code>
       clause of the
       <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
        <code class="literal">
         CREATE INDEX
        </code>
       </a>
       statement. For
            example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13467892"><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>id <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> id_index <span class="token keyword">ON</span> t1 <span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">COMMENT</span> <span class="token string">'MERGE_THRESHOLD=40'</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       If the page-full percentage for an index page falls below
            the
       <code class="literal">
        MERGE_THRESHOLD
       </code>
       value when a row is
            deleted or when a row is shortened by an update operation,
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       attempts to merge the
            index page with a neighboring index page. The default
       <code class="literal">
        MERGE_THRESHOLD
       </code>
       value is 50, which is the
            previously hardcoded value.
      </p>
      <p>
       <code class="literal">
        MERGE_THRESHOLD
       </code>
       can also be defined at
            the index level and table level using
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
        </code>
       </a>
       and
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       statements. For
            more information, see
       <a class="xref" href="index-page-merge-threshold.html" title="17.8.11 Configuring the Merge Threshold for Index Pages">
        Section 17.8.11, “Configuring the Merge Threshold for Index Pages”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        VISIBLE
       </code>
       ,
       <code class="literal">
        INVISIBLE
       </code>
      </p>
      <p>
       Specify index visibility. Indexes are visible by default. An
            invisible index is not used by the optimizer. Specification
            of index visibility applies to indexes other than primary
            keys (either explicit or implicit). For more information,
            see
       <a class="xref" href="invisible-indexes.html" title="10.3.12 Invisible Indexes">
        Section 10.3.12, “Invisible Indexes”
       </a>
       .
      </p>
      <a class="indexterm" name="idm46045187367392">
      </a>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        ENGINE_ATTRIBUTE
       </code>
       and
       <code class="literal">
        SECONDARY_ENGINE_ATTRIBUTE
       </code>
       are used to
            specify index attributes for primary and secondary storage
            engines. The options are reserved for future use.
      </p>
      <p>
       The value assigned to this option is a string literal
            containing a valid JSON document or an empty string ('').
            Invalid JSON is rejected.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa58046435"><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">INDEX</span> i1 <span class="token keyword">ON</span> t1 <span class="token punctuation">(</span>c1<span class="token punctuation">)</span> <span class="token keyword">ENGINE_ATTRIBUTE</span><span class="token operator">=</span><span class="token string">'{"<em class="replaceable">key</em>":"<em class="replaceable">value</em>"}'</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       <code class="literal">
        ENGINE_ATTRIBUTE
       </code>
       and
       <code class="literal">
        SECONDARY_ENGINE_ATTRIBUTE
       </code>
       values can be
            repeated without error. In this case, the last specified
            value is used.
      </p>
      <p>
       <code class="literal">
        ENGINE_ATTRIBUTE
       </code>
       and
       <code class="literal">
        SECONDARY_ENGINE_ATTRIBUTE
       </code>
       values are not
            checked by the server, nor are they cleared when the
            table's storage engine is changed.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="create-index-copying">
       </a>
       Table Copying and Locking Options
      </h4>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     ALGORITHM
    </code>
    and
    <code class="literal">
     LOCK
    </code>
    clauses
        may be given to influence the table copying method and level of
        concurrency for reading and writing the table while its indexes
        are being modified. They have the same meaning as for the
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    statement. For more
        information, see
    <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     Section 15.1.9, “ALTER TABLE Statement”
    </a>
   </p>
   <p>
    NDB Cluster supports online operations using the same
    <code class="literal">
     ALGORITHM=INPLACE
    </code>
    syntax used with the
        standard MySQL Server. See
    <a class="xref" href="mysql-cluster-online-operations.html" title="25.6.12 Online Operations with ALTER TABLE in NDB Cluster">
     Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster”
    </a>
    , for more
        information.
   </p>
  </div>
 </div>
 <br/>
</div>
