MySQL spatial slowed after upgrade to 8

Question

After I updated MySQL from 5.7 to 8, spatial functions have become very slow. Specifically, this query:

SELECT points3_.track_id AS track_id
FROM waypoint points3_ 
WHERE ST_CONTAINS(ST_POLYFROMTEXT('POLYGON ((34.82041 32.041814, 34.82041 32.158149, 35.47959 32.158149, 35.47959 32.041814, 34.82041 32.041814))'), points3_.point)

now uses a full table scan and takes 6 seconds on 500,000 records, while before it took milliseconds.

I have a spatial index on point column, but it's not activated even if I enter force index (point)

Answer 1

When you upgrade from mysql 5.7 to 8, you have to modify geometry columns to force an SRID.

From the Ref Manual:

1. 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.

2. Redefine the column to have an explicit SRID attribute.

3. Recreate the SPATIAL index.

https://dev.mysql.com/doc/refman/8.0/en/spatial-index-optimization.html