<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="creating-spatial-indexes">
      </a>
      13.4.10 Creating Spatial Indexes
     </h3>
    </div>
   </div>
  </div>
  <p>
   For
   <code class="literal">
    InnoDB
   </code>
   and
   <code class="literal">
    MyISAM
   </code>
   tables, MySQL can create spatial indexes using syntax similar to
        that for creating regular indexes, but using the
   <code class="literal">
    SPATIAL
   </code>
   keyword. Columns in spatial indexes
        must be declared
   <code class="literal">
    NOT NULL
   </code>
   . The following
        examples demonstrate how to create spatial indexes:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      With
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa53434364"><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> geom <span class="token punctuation">(</span>g <span class="token keyword">GEOMETRY</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">SRID</span> <span class="token number">4326</span><span class="token punctuation">,</span> <span class="token keyword">SPATIAL</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>g<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      With
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20028546"><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> geom <span class="token punctuation">(</span>g <span class="token keyword">GEOMETRY</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">SRID</span> <span class="token number">4326</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> geom <span class="token keyword">ADD</span> <span class="token keyword">SPATIAL</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>g<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      With
      <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
       <code class="literal">
        CREATE INDEX
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66166939"><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> geom <span class="token punctuation">(</span>g <span class="token keyword">GEOMETRY</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">SRID</span> <span class="token number">4326</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">SPATIAL</span> <span class="token keyword">INDEX</span> g <span class="token keyword">ON</span> geom <span class="token punctuation">(</span>g<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   <code class="literal">
    SPATIAL INDEX
   </code>
   creates an R-tree index. For
        storage engines that support nonspatial indexing of spatial
        columns, the engine creates a B-tree index. A B-tree index on
        spatial values is useful for exact-value lookups, but not for
        range scans.
  </p>
  <p>
   The optimizer can use spatial indexes defined on columns that
        are SRID-restricted. For more information, see
   <a class="xref" href="spatial-type-overview.html" title="13.4.1 Spatial Data Types">
    Section 13.4.1, “Spatial Data Types”
   </a>
   , and
   <a class="xref" href="spatial-index-optimization.html" title="10.3.3 SPATIAL Index Optimization">
    Section 10.3.3, “SPATIAL Index Optimization”
   </a>
   .
  </p>
  <p>
   For more information on indexing spatial columns, see
   <a class="xref" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    Section 15.1.15, “CREATE INDEX Statement”
   </a>
   .
  </p>
  <p>
   To drop spatial indexes, use
   <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="drop-index.html" title="15.1.27 DROP INDEX Statement">
    <code class="literal">
     DROP INDEX
    </code>
   </a>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      With
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa8054621"><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">ALTER</span> <span class="token keyword">TABLE</span> geom <span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> g<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      With
      <a class="link" href="drop-index.html" title="15.1.27 DROP INDEX Statement">
       <code class="literal">
        DROP INDEX
       </code>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa33833694"><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">DROP</span> <span class="token keyword">INDEX</span> g <span class="token keyword">ON</span> geom<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Example: Suppose that a table
   <code class="literal">
    geom
   </code>
   contains
        more than 32,000 geometries, which are stored in the column
   <code class="literal">
    g
   </code>
   of type
   <code class="literal">
    GEOMETRY
   </code>
   . The
        table also has an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column
   <code class="literal">
    fid
   </code>
   for storing object ID values.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56841133"><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">DESCRIBE</span> geom<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>
<span class="token output"><span class="token punctuation">|</span> Field <span class="token punctuation">|</span> Type     <span class="token punctuation">|</span> Null <span class="token punctuation">|</span> Key <span class="token punctuation">|</span> Default <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>
<span class="token output"><span class="token punctuation">|</span> fid   <span class="token punctuation">|</span> int(11)  <span class="token punctuation">|</span>      <span class="token punctuation">|</span> PRI <span class="token punctuation">|</span> NULL    <span class="token punctuation">|</span> auto_increment <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> g     <span class="token punctuation">|</span> geometry <span class="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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 function">COUNT</span><span class="token punctuation">(</span><span class="token operator">*</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> geom<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> count(*) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>    32376 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   To add a spatial index on the column
   <code class="literal">
    g
   </code>
   , use
        this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa19208646"><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> geom <span class="token keyword">ADD</span> <span class="token keyword">SPATIAL</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>g<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 32376 rows affected (4.05 sec)</span>
<span class="token output">Records: 32376  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
 </div>
 <br/>
</div>
