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.

Answer 1

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