<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="spatial-index-optimization">
      </a>
      10.3.3 SPATIAL Index Optimization
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045227504976">
  </a>
  <a class="indexterm" name="idm46045227503488">
  </a>
  <p>
   MySQL permits creation of
   <code class="literal">
    SPATIAL
   </code>
   indexes on
   <code class="literal">
    NOT NULL
   </code>
   geometry-valued columns (see
   <a class="xref" href="creating-spatial-indexes.html" title="13.4.10 Creating Spatial Indexes">
    Section 13.4.10, “Creating Spatial Indexes”
   </a>
   ). The optimizer
        checks the
   <code class="literal">
    SRID
   </code>
   attribute for indexed columns
        to determine which spatial reference system (SRS) to use for
        comparisons, and uses calculations appropriate to the SRS.
        (Prior to MySQL 8.4, the optimizer performs
        comparisons of
   <code class="literal">
    SPATIAL
   </code>
   index values using
        Cartesian calculations; the results of such operations are
        undefined if the column contains values with non-Cartesian
        SRIDs.)
  </p>
  <p>
   For comparisons to work properly, each column in a
   <code class="literal">
    SPATIAL
   </code>
   index must be SRID-restricted. That
        is, the column definition must include an explicit
   <code class="literal">
    SRID
   </code>
   attribute, and all column values must
        have the same SRID.
  </p>
  <p>
   The optimizer considers
   <code class="literal">
    SPATIAL
   </code>
   indexes only
        for SRID-restricted columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Indexes on columns restricted to a Cartesian SRID enable
            Cartesian bounding box computations.
     </p>
    </li>
    <li class="listitem">
     <p>
      Indexes on columns restricted to a geographic SRID enable
            geographic bounding box computations.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The optimizer ignores
   <code class="literal">
    SPATIAL
   </code>
   indexes on
        columns that have no
   <code class="literal">
    SRID
   </code>
   attribute (and thus
        are not SRID-restricted). MySQL still maintains such indexes, as
        follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      They are updated for table modifications
            (
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      ,
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      ,
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      , and so forth).
            Updates occur as though the index was Cartesian, even though
            the column might contain a mix of Cartesian and geographical
            values.
     </p>
    </li>
    <li class="listitem">
     <p>
      They exist only for backward compatibility (for example, the
            ability to perform a dump in MySQL
            8.2 and restore in MySQL
            8.3). Because
      <code class="literal">
       SPATIAL
      </code>
      indexes on columns that are not SRID-restricted are of no
            use to the optimizer, each such column should be modified:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Verify that all values within the column have the same
                SRID. To determine the SRIDs contained in a geometry
                column
         <em class="replaceable">
          <code>
           col_name
          </code>
         </em>
         , use the
                following query:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa79425927"><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 keyword">DISTINCT</span> <span class="token function">ST_SRID</span><span class="token punctuation">(</span><em class="replaceable">col_name</em><span class="token punctuation">)</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span></code></pre>
        </div>
        <p>
         If the query returns more than one row, the column
                contains a mix of SRIDs. In that case, modify its
                contents so all values have the same SRID.
        </p>
       </li>
       <li class="listitem">
        <p>
         Redefine the column to have an explicit
         <code class="literal">
          SRID
         </code>
         attribute.
        </p>
       </li>
       <li class="listitem">
        <p>
         Recreate the
         <code class="literal">
          SPATIAL
         </code>
         index.
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
