-1

I'm able to have both versions 5.7 and 8.0 of MySQL running on the same hardware (Windows 10 with NVMe SSD). The following query is over 1000 times faster to execute under MySQL 5.7 than MySQL 8.0:

SELECT `oacode`,ST_asWKB(`shape`) as `shape` FROM 
    (SELECT * FROM oa_bdy WHERE seatname='Barnsley Central') AS `vtable`
    WHERE MBRIntersects(`shape`,ST_GeomFromText('POLYGON ((426519 410113, 426519 4156675,
    432073 415667, 432073 410113, 426519 410113))', 27700));

It takes 0.016 secs to execute on MySQL 5.7, but 19.6 secs on MySQL 8.0.

The tables use MyISAM, and the oa_bdy table has 232,296 rows, but only 290 of those match the inner WHERE. One difference is that 'EXPLAIN' on the query produces different results from the two versions.

MySQL id select_type table partitions type possible_keys key key_len ref rows filtered Extra
v5.7 1 SIMPLE oa_bdy (NULL) range shape shape 34 (NULL) 1406 10.00 Using where
v8.0 1 SIMPLE oa_bdy (NULL) ALL (NULL) (NULL) (NULL) (NULL) 232296 10.00 Using where

I don't know enough about the MySQL internals to resolve this, but the performance difference is quite significant and detrimental.

Does anyone have any suggestions?

Thanks.

Update (7-Feb-2021). Thanks for the comment about spatial index. The schema for both versions was the same (one was a copy of the other) and the schema does contain a spatial index.

CREATE TABLE `oa_bdy` (
   `shape` geometry NOT NULL,
   `oacode` varchar(9) NOT NULL,
   `seatname` varchar(43) DEFAULT NULL,
   PRIMARY KEY (`oacode`),
   SPATIAL KEY `shape` (`shape`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8

One problem seems to be that there is a new behaviour in MySQL 8 which will ignore the spatial index (for query optimization purposes) unless the column has a defined SRID (rather than just the individual cells). When the schema under 8.0 is changed to

CREATE TABLE `oa_bdy` (
   `shape` geometry NOT NULL SRID 27700,
   `oacode` varchar(9) NOT NULL,
   `seatname` varchar(43) DEFAULT NULL,
   PRIMARY KEY (`oacode`),
   SPATIAL KEY `shape` (`shape`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8

then the EXPLAIN under 8.0 matches that of 5.7, and the run-time under 8.0 drops to 0.391 secs, which is still 24 times slower than MySQL 5.7.

Is there any way of getting the run-times under MySQL 8.0 to be similar to those of MySQL 5.7 for this sort of query?

Martin Baxter
  • 109
  • 2
  • 6
  • One suggestion for MySQL 8 is to use EXPLAIN ANALYZE. EXPLAIN ANALYZE will actually run the query and let you know if the predicted execution plan matched reality. Another thing to consider would be if both versions have the same keys and indices applied? – Kurt Kline Feb 06 '21 at 12:54
  • 4
    Please post `SHOW CREATE TABLE oa_bdy` from both MySQL versions. That should hopefully reveal the missing index right away – Michael Berkowski Feb 06 '21 at 13:10
  • So the index is supposedly there. This is weird. Maybe drop the index and recreate it or `REPAIR TABLE oa_bdy` (I think that works for MyISAM). – Michael Berkowski Feb 07 '21 at 15:20
  • Or the `SHOW` will point out that the 8.0 version is actually using InnoDB. – Rick James Feb 08 '21 at 19:30
  • If everything matches except the timing, please file a bug report: bugs.mysql.com – Rick James Feb 08 '21 at 19:33
  • Thanks, Rick. Bug-report #102599 has been posted. – Martin Baxter Feb 15 '21 at 12:30

1 Answers1

3

You definitely have an index on your older database that isn't there on the newer one. Due to the lack of index on the new database, the only way MySQL can satisfy your query is to compare every row in the table to your search criterion. With the right index, the number of rows needing comparing is much smaller.

Do SHOW CREATE TABLE oa_bdy on both 5.7 and 8.0, and compare them. The older one will surely mention one or more indexes.

You can create an index to help with the query you showed us by doing this ....

CREATE SPATIAL INDEX x_oa_bdy_shape ON oa_bdy(shape);
O. Jones
  • 103,626
  • 17
  • 118
  • 172