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
SHOW CREATE TABLE <table>output for both MySQL servers. Also provide us withEXPLAIN <query>for both servers, we would like to verify.. – Holleosm_roadtable in the query you pasted the create structure fromcountry– HollePRIMARY KEYis aUNIQUEkey, so you have a redundant index onroadid. – Friedainnodb_buffer_pool_sizeon each server? And the amount of RAM on each? – Frieda