MySql 8.0.11 spatial queries slow by a factor of 100000

Question

We have migrated a copy of a MySql 5.7 database to MySql 8.0.11 on Amazon RDS. Where possible everything is identical. A table containing geometry data has been modified so the geometry column is restricted to SRID 0 and the spatial index rebuilt. The execution plan on both databases are identical and indicate the query is using the spatial index.

On the MySql 5.7 the following query takes 0.001s on MySql 8 it takes 108s. Looking at the execution statistics ‘Sending data’ account for 100% of the time on MySql 8. Why?

SELECT r.roadid                             
FROM geocoder.osm_road r
WHERE mbrintersects(ST_Buffer(ST_GEOMETRYFROMTEXT('Point(-1.91289 52.58260)',0),0.0005), r.geometry) 

We have many different spatial queries, and all are behaving like this, but in particular MBRINTERSECTS and ST_INTERSECTS seem incredibly slow. Changing to ST_CONTAINS (where possible) makes a significant improvement (ie the above takes 3s instead of 108s), but this is not suitable for many queries and is still slower than in 5.7 by a significant margin.

Show Create Table for 5.7

CREATE TABLE `osm_road` (
  `roadid` bigint(20) NOT NULL,
  `reference` varchar(20) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `dictionary` longtext,
  `road_speed_limit` tinyint(4) unsigned NOT NULL,
  `road_speed_unitid` tinyint(1) NOT NULL,
  `road_type` tinyint(4) NOT NULL,
  `is_toll_road` bit(1) NOT NULL DEFAULT b'0',
  `is_one_way` bit(1) NOT NULL DEFAULT b'0',
  `countryid` smallint(6) DEFAULT NULL,
  `geometry` geometry NOT NULL,
  `datemodified` datetime DEFAULT CURRENT_TIMESTAMP,
  `datecreated` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`roadid`),
  UNIQUE KEY `id_roadid` (`roadid`),
  SPATIAL KEY `ix_road_geometry` (`geometry`),
  KEY `ix_road_reference` (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=207


Show Create Table for 8.0.11

CREATE TABLE `osm_road` (
  `roadid` bigint(20) NOT NULL,
  `reference` varchar(20) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `dictionary` longtext,
  `road_speed_limit` tinyint(4) unsigned NOT NULL,
  `road_speed_unitid` tinyint(1) NOT NULL,
  `road_type` tinyint(4) NOT NULL,
  `is_toll_road` bit(1) NOT NULL DEFAULT b'0',
  `is_one_way` bit(1) NOT NULL DEFAULT b'0',
  `countryid` smallint(6) DEFAULT NULL,
  `geometry` geometry NOT NULL /*!80003 SRID 0 */,
  `datemodified` datetime DEFAULT CURRENT_TIMESTAMP,
  `datecreated` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`roadid`),
  UNIQUE KEY `id_roadid` (`roadid`),
  KEY `ix_road_reference` (`reference`),
  SPATIAL KEY `ix_road_geometry` (`geometry`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=207

Show Index From osm_road (5.7)

Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment,Index_comment
osm_road,0,PRIMARY,1,roadid,A,18973144,NULL,NULL,,BTREE,,
osm_road,1,ix_road_geometry,1,geometry,A,18973154,32,NULL,,SPATIAL,,
osm_road,1,ix_road_reference,1,reference,A,199900,NULL,NULL,YES,BTREE,,

Show Index From osm_road (8.0.11)

Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality,Sub_part,Packed,Null,Index_type,Comment,Index_comment,Visible
osm_road,0,PRIMARY,1,roadid,A,16194921,NULL,NULL,,BTREE,,,YES
osm_road,1,ix_road_reference,1,reference,A,86215,NULL,NULL,YES,BTREE,,,YES
osm_road,1,ix_road_geometry,1,geometry,A,16194921,32,NULL,,SPATIAL,,,YES

Show Global Status (8.0.11) Click Here

Show Global Variables (8.0.11) Click Here

-- Added 2019-01-27 -----------------

EXPLAIN SELECT r.roadid FROM geocoder.osm_road r WHERE mbrintersects(ST_Buffer(ST_GEOMETRYFROMTEXT('Point(-1.91289 52.58260)',0),0.0005), r.geometry); 

Returns:

select_type:   Simple
table:         r
partitions:    null
possible_keys: ix_road_geometry
key:           ix_road_geometry
key_len:       34
ref:           null
rows:          1
filtered:      100
Extra:         Using where

Answer 1

MBRIntersects and ST_Intersects are broken in MySQL 8.0 (tested in 8.0.15) and are more than 10x slower compared to MySQL 5.7. For me disabling the use of the spatial index (using IGNORE INDEX, forcing a full table scan, speeds up my queries somewhat. See this bug report https://bugs.mysql.com/bug.php?id=94655