<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="mysql-shell-tutorial-python-documents-index">
      </a>
      22.4.3.6 Create and Drop Indexes
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045126989920">
  </a>
  <p>
   Indexes are used to find documents with specific field values
        quickly. Without an index, MySQL must begin with the first
        document and then read through the entire collection to find the
        relevant fields. The larger the collection, the more this costs.
        If a collection is large and queries on a specific field are
        common, then consider creating an index on a specific field
        inside a document.
  </p>
  <p>
   For example, the following query performs better with an index
        on the Population field:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-mysqlsh"><div class="docs-select-all right" id="sa77747666"><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-mysqlsh"><span class="token prompt">mysql-py&gt;</span> db<span class="token punctuation">.</span>countryinfo<span class="token punctuation">.</span><span class="token function">find</span><span class="token punctuation">(</span><span class="token string">"demographics.Population &lt; 100"</span><span class="token punctuation">)</span>
<span class="token operator">...</span><span class="token punctuation">[</span><em>output removed</em><span class="token punctuation">]</span>
<span class="token output">8 documents in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    create_index()
   </code>
   method creates an index
        that you can define with a JSON document that specifies which
        fields to use. This section is a high level overview of
        indexing. For more information see
   <a class="ulink" href="/doc/x-devapi-userguide/en/collection-indexing.html" target="_top">
    Indexing Collections
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="mysql-shell-tutorial-python-nonunique-index">
       </a>
       Add a Nonunique Index
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045126981568">
   </a>
   <p>
    To create a nonunique index, pass an index name and the index
          information to the
    <code class="literal">
     create_index()
    </code>
    method.
          Duplicate index names are prohibited.
   </p>
   <p>
    The following example specifies an index named
    <code class="literal">
     popul
    </code>
    , defined against the
    <code class="literal">
     Population
    </code>
    field from the
    <code class="literal">
     demographics
    </code>
    object, indexed as an
    <code class="literal">
     Integer
    </code>
    numeric value. The final parameter
          indicates whether the field should require the
    <code class="literal">
     NOT
          NULL
    </code>
    constraint. If the value is
    <code class="literal">
     false
    </code>
    , the field can contain
    <code class="literal">
     NULL
    </code>
    values. The index information is a
          JSON document with details of one or more fields to include in
          the index. Each field definition must include the full
          document path to the field, and specify the type of the field.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-mysqlsh"><div class="docs-select-all right" id="sa10732819"><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-mysqlsh"><span class="token prompt">mysql-py&gt;</span> db<span class="token punctuation">.</span>countryinfo<span class="token punctuation">.</span><span class="token function">createIndex</span><span class="token punctuation">(</span><span class="token string">"popul"</span><span class="token punctuation">,</span> <span class="token punctuation">{</span>fields<span class="token punctuation">:</span>
<span class="token punctuation">[</span><span class="token punctuation">{</span>field<span class="token punctuation">:</span> <span class="token string">'$.demographics.Population'</span><span class="token punctuation">,</span> type<span class="token punctuation">:</span> <span class="token string">'INTEGER'</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>
    Here, the index is created using an integer numeric value.
          Further options are available, including options for use with
          GeoJSON data. You can also specify the type of index, which
          has been omitted here because the default type
    <span class="quote">
     “
     <span class="quote">
      index
     </span>
     ”
    </span>
    is appropriate.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="mysql-shell-tutorial-python-unique-index">
       </a>
       Add a Unique Index
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045126968736">
   </a>
   <p>
    To create a unique index, pass an index name, the index
          definition, and the index type
    <span class="quote">
     “
     <span class="quote">
      unique
     </span>
     ”
    </span>
    to the
    <code class="literal">
     create_index()
    </code>
    method. This example shows a
          unique index created on the country name
          (
    <code class="literal">
     "Name"
    </code>
    ), which is another common field in
          the
    <code class="literal">
     countryinfo
    </code>
    collection to index. In the
          index field description,
    <code class="literal">
     "TEXT(40)"
    </code>
    represents the number of characters to index, and
    <code class="literal">
     "required": True
    </code>
    specifies that the field
          is required to exist in the document.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-mysqlsh"><div class="docs-select-all right" id="sa34610087"><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-mysqlsh"><span class="token prompt">mysql-py&gt;</span> db<span class="token punctuation">.</span>countryinfo<span class="token punctuation">.</span><span class="token function">create_index</span><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">,</span>
<span class="token punctuation">{</span><span class="token string">"fields"</span><span class="token punctuation">:</span> <span class="token punctuation">[</span><span class="token punctuation">{</span><span class="token string">"field"</span><span class="token punctuation">:</span> <span class="token string">"$.Name"</span><span class="token punctuation">,</span> <span class="token string">"type"</span><span class="token punctuation">:</span> <span class="token string">"TEXT(40)"</span><span class="token punctuation">,</span> <span class="token string">"required"</span><span class="token punctuation">:</span> True<span class="token punctuation">}</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string">"unique"</span><span class="token punctuation">:</span> True<span class="token punctuation">}</span><span class="token punctuation">)</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="mysql-shell-tutorial-python-drop-index">
       </a>
       Drop an Index
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045126959408">
   </a>
   <p>
    To drop an index, pass the name of the index to drop to the
    <code class="literal">
     drop_index()
    </code>
    method. For example, you can
          drop the
    <span class="quote">
     “
     <span class="quote">
      popul
     </span>
     ”
    </span>
    index as follows:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-mysqlsh"><div class="docs-select-all right" id="sa92279514"><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-mysqlsh"><span class="token prompt">mysql-py&gt;</span> db<span class="token punctuation">.</span>countryinfo<span class="token punctuation">.</span><span class="token function">drop_index</span><span class="token punctuation">(</span><span class="token string">"popul"</span><span class="token punctuation">)</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="idm46045126954464">
       </a>
       Related Information
      </h5>
     </div>
    </div>
   </div>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       See
       <a class="ulink" href="/doc/x-devapi-userguide/en/collection-indexing.html" target="_top">
        Indexing Collections
       </a>
       for more
              information.
      </p>
     </li>
     <li class="listitem">
      <p>
       See
       <a class="ulink" href="/doc/x-devapi-userguide/en/collection-indexing.html#collection-index-definitions" target="_top">
        Defining an Index
       </a>
       for
              more information on the JSON document that defines an
              index.
      </p>
     </li>
     <li class="listitem">
      <p>
       See
       <a class="ulink" href="/doc/x-devapi-userguide/en/crud-ebnf-collection-index-management-functions.html" target="_top">
        Collection Index Management Functions
       </a>
       for the full syntax definition.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
