CODEWITHSUNDEEP
mysqlindexingspatial
Arbaz Sheikh
Arbaz Sheikh

Reputation: 257

MySql 8 is not using spatial index

I have spatial column named SHAPE with SRID 4269 and spatial index. When i make the query

select geoid10 as zipcode from tl_2019_us_zcta510
   where st_intersects(ST_GeomFromText('POINT(30.330280 -82.759009)',4269),SHAPE);

it takes 2 minutes to run. The table contains 33k records.

I checked the query whether it is using index

explain select geoid10 as zipcode from tl_2019_us_zcta510
   where st_intersects(ST_GeomFromText('POINT(30.330280 -82.759009)',4269),SHAPE);

and i get the result

+----+-------------+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | tl_2019_us_zcta510 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 28206 |   100.00 | Using where |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+

This clearly shows that the query is not using spatial index.

I ran the same query in Mysql 5.7 and it is using spatial index.

Can anybody help me with this. Is there any other configurational change i should look out for.

Upvotes: 4

Views: 2468

Answers (2)

dAm2K
dAm2K

Reputation: 10329

I solved after I read this one: https://dba.stackexchange.com/questions/260757/mysql-8-not-using-spatial-index

Basically, you need to define a default SRID for your column at table creation (or with ALTER).

In your case, the column geoid10 in table tl_2019_us_zcta510 should be defined with SRID 4269, then it should correctly use the spatial index. It worked for me.

This is the correct answer quoted from Nikita at dba.stackexchange.com:

The SRID attribute makes a spatial column SRID-restricted, which has these implications:

The column can contain only values with the given SRID. Attempts to insert values with a different SRID produce an error.

The optimizer can use SPATIAL indexes on the column. See Section 8.3.3, “SPATIAL Index Optimization”.

Spatial columns with no SRID attribute are not SRID-restricted and accept values with any SRID. However, the optimizer cannot use SPATIAL indexes on them until the column definition is modified to include an SRID attribute, which may require that the column contents first be modified so that all values have the same SRID.

Upvotes: 0

Rick James
Rick James

Reputation: 142258

(This does not answer the question, but may add some insight.)

The 8.0.4 Changelog says (I added bolding):

Incompatible Change: Previously, these spatial functions ignored the spatial reference system (SRS) for geometry arguments and computed results on a Cartesian plane. They now support computations for geometry arguments that specify a geographic SRS: ST_Distance_Sphere(), ST_IsSimple(), ST_IsValid(), ST_Length().

Previously, these spatial functions ignored the SRS for any geometry arguments and computed results on a Cartesian plane. They now produce an error when invoked with geometry arguments that specify a geographic SRS: ST_Area(), ST_Buffer(), ST_Centroid(), ST_ConvexHull(), ST_Difference(), ST_Envelope(), ST_Intersection(), ST_IsClosed(), ST_MakeEnvelope(), ST_Simplify(), ST_SymDifference(), ST_Union(), ST_Validate().

Previously, these spatial functions permitted geometry arguments with an undefined SRS. They now produce an error when invoked with geometry arguments that have an undefined SRS: ST_Dimension(), ST_Distance_Sphere(), ST_EndPoint(), ST_ExteriorRing(), ST_GeometryN(), ST_GeometryType(), ST_InteriorRingN(), ST_IsEmpty(), ST_IsSimple(), ST_IsValid(), ST_Length(), ST_NumGeometries(), ST_NumInteriorRing(), ST_NumInteriorRings(), ST_NumPoints(), ST_PointN(), ST_StartPoint(), ST_SwapXY(), ST_X(), ST_Y().

Previously, the ST_GeoHash() spatial function accepted points with any SRID. ST_GeoHash() now accepts only points with SRID 0 or 4326. Note

If spatial data contains geometry values that now are interpreted differently by the functions just listed, existing queries using these functions will return different results, compared to previous MySQL versions.

My comment: What is 4269? Perhaps only 4326 is handled? Will it run faster with 4326?

Upvotes: 3

Related Questions