MySql 8 is not using spatial index

Question

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.

Rick James · Accepted Answer · 2020-06-10 18:55:38Z

3

(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?

answered Jun 10, 2020 at 18:55

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arbaz Sheikh Over a year ago

I have another doubt. doing this using innodb engine works fine. But as I am using mysql ndb cluster, i want to use NDB engine so that data gets replicated across nodes. As ndb doesnt support spatial reference system, what should be the alternate solution?

Rick James Over a year ago

Replication is available with InnoDB. Please explain further why you picked NDB. (NDB serves a small niche; InnoDB serves a broad range of applications.)

Arbaz Sheikh Over a year ago

i wanted HA for 3 host setup with no single point of failure. Also all hosts should perform both read and write

Rick James Over a year ago

@ArbazSheikh - See Galera (included with MariaDB) which allows read and write on 3+ nodes. or Group Replication / Innodb Cluster (from Oracle). Both have no SPOF and do automatic failover. Both handle InnoDB and SPATIAL (MariaDB 10.2 / MySQL 5.7)

dAm2K · Accepted Answer · 2020-10-03 21:27:21Z

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.

Collectives™ on Stack Overflow

MySql 8 is not using spatial index

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related