Description:
Having some major performance issues with queries that were fine in 5.7.30 before migrating to Ubuntu 20.04 / MySQL 8.0.20. Execution is taking roughly 3x as long to complete.
How to repeat:
I've been able to reproduce issue simply by spinning up to clean instances of Ubuntu 16.04 and Ubuntu 20.04, installing mysql server, importing test data and executing a couple of queries.
Test data: https://zuma-design.com/shared/test.sql
TABLEs (included in test data):
CREATE TABLE `oc_product_to_store` (
`product_id` int NOT NULL,
`store_id` int NOT NULL,
PRIMARY KEY (`product_id`,`store_id`),
KEY `product_id` (`product_id`),
KEY `store_id` (`store_id`),
KEY `store_product` (`store_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `oc_product_to_vehicle` (
`product_to_vehicle_id` int NOT NULL AUTO_INCREMENT,
`product_id` int NOT NULL,
`vehicle_id` mediumint NOT NULL DEFAULT '0',
`submodel_id` smallint NOT NULL DEFAULT '0',
PRIMARY KEY (`product_to_vehicle_id`),
KEY `submodel_id` (`submodel_id`),
KEY `product_vehicle_submodel` (`product_id`,`vehicle_id`,`submodel_id`),
KEY `vehicle_product` (`vehicle_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `oc_vehicle_type` (
`vehicle_type_id` smallint NOT NULL AUTO_INCREMENT,
`vehicle_type_name` varchar(64) NOT NULL,
PRIMARY KEY (`vehicle_type_id`),
KEY `vehicle_type_name` (`vehicle_type_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `oc_base_vehicle` (
`vehicle_id` mediumint NOT NULL AUTO_INCREMENT,
`year` smallint NOT NULL DEFAULT '0',
`make_id` smallint NOT NULL DEFAULT '0',
`model_id` mediumint NOT NULL DEFAULT '0',
`vehicle_type_id` smallint NOT NULL DEFAULT '0',
PRIMARY KEY (`vehicle_id`),
KEY `make_id` (`make_id`),
KEY `model_id` (`model_id`),
KEY `year_make` (`year`,`make_id`),
KEY `year_model` (`year`,`model_id`),
KEY `vehicle_type_id` (`vehicle_type_id`),
KEY `ymm` (`year`,`make_id`,`model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Queries to compare between versions:
SELECT DISTINCT vehicle_id, submodel_id, store_id
FROM product_to_store pts
JOIN product_to_vehicle ptv USING (product_id)
WHERE vehicle_id != 0 AND pts.store_id = 21;
This one takes me roughly 8 seconds on 5.7.30 and over 30 seconds on 8.0.20.
SELECT DISTINCT vehicle_type_id, vehicle_type_name
FROM base_vehicle bv
INNER JOIN vehicle_type vt USING (vehicle_type_id);
This one yields identical explain on both servers but averages 0.07 sec on MySQL 5.7 and 0.30 Sec on MySQL 8, roughly 4 times as long;
