MySQL 9.2 Release Notes
MySQL permits creation of SPATIAL indexes on
NOT NULL geometry-valued columns (see
Section 13.4.10, “Creating Spatial Indexes”). The optimizer
checks the SRID 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 9.2, the optimizer performs
comparisons of SPATIAL index values using
Cartesian calculations; the results of such operations are
undefined if the column contains values with non-Cartesian
SRIDs.)
For comparisons to work properly, each column in a
SPATIAL index must be SRID-restricted. That
is, the column definition must include an explicit
SRID attribute, and all column values must
have the same SRID.
The optimizer considers SPATIAL indexes only
for SRID-restricted columns:
Indexes on columns restricted to a Cartesian SRID enable Cartesian bounding box computations.
Indexes on columns restricted to a geographic SRID enable geographic bounding box computations.
The optimizer ignores SPATIAL indexes on
columns that have no SRID attribute (and thus
are not SRID-restricted). MySQL still maintains such indexes, as
follows:
They are updated for table modifications (
INSERT,UPDATE,DELETE, and so forth). Updates occur as though the index was Cartesian, even though the column might contain a mix of Cartesian and geographical values.They exist only for backward compatibility (for example, the ability to perform a dump in MySQL 8.4 and restore in MySQL 9.1). Because
SPATIALindexes on columns that are not SRID-restricted are of no use to the optimizer, each such column should be modified:Verify that all values within the column have the same SRID. To determine the SRIDs contained in a geometry column
col_name, use the following query:SELECT DISTINCT ST_SRID(col_name) FROM tbl_name;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.
Redefine the column to have an explicit
SRIDattribute.Recreate the
SPATIALindex.