Bug #109942 Query degradation when upgrade from mysql 5.6 to 5.7.12
Submitted: 6 Feb 2023 6:39 Modified: 6 Feb 2023 10:14
Reporter: Ravi SHARMA Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:5.7.12 OS:Linux
Assigned to: CPU Architecture:Any

[6 Feb 2023 6:39] Ravi SHARMA 
Description:
We have recently upgraded from 5.6 to 5.7.12 and saw there is significant(from 36ms to 9 sec) degradation in MySQL query:

SELECT 
	`roamer_request`.* FROM `roamer_request` 
	LEFT JOIN `store` ON `roamer_request`.`store_id` = `store`.`id` 
    LEFT JOIN `store_settings` ON `store`.`id` = `store_settings`.`store_id` 
    JOIN `roaming_area_setting` ON roamer_request.roaming_area_id = roaming_area_setting.roaming_areas_id 
    LEFT JOIN `pickup_reassign` ON pickup_reassign.roamerRequestId = roamer_request.id 
WHERE (`roamer_request`.`isArchived`=0) 
	AND ((`roamer_request`.`status`=0) OR (`roamer_request`.`sub_status` IN ('roaming_areas_has_roamer_storePending reassign', 'Pending'))) 
    AND (`store_settings`.`value` LIKE '%car%') 
    AND (`roamer_request`.`is_broadcast`=1) 
    AND (`roamer_request`.`auto_allocation_lock`=0) 
    AND (`roaming_area_setting`.`isArchived`=0) 
    AND (`roaming_area_setting`.`pure_roaming_enabled`=1) 
    AND (`store`.`isArchived`=0) 
    AND ((`store_settings`.`isArchived`=0) AND (`store_settings`.`name`='roamer_vehicles') ) 

How to repeat:
Just create these tables and run the query; you will see the difference even with empty tables.

One refers: https://recordit.co/LgkZf9zdNa 

CREATE TABLE `roamer_request` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_id` int(11) NOT NULL,
  `driver_id` int(11) DEFAULT NULL,
  `order_numbers` smallint(2) DEFAULT NULL,
  `status` smallint(2) DEFAULT NULL,
  `createdAt` int(11) DEFAULT NULL,
  `updatedAt` int(11) DEFAULT NULL,
  `isArchived` smallint(1) DEFAULT '0',
  `payment_schedule_id` int(11) DEFAULT NULL,
  `orders_reference` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_pickup_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_pickup_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sub_status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `run_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shift_id` int(11) DEFAULT NULL,
  `createdByType` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `createdById` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uuid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `store_position` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cancel_message` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `to_store_coordinates` mediumtext COLLATE utf8_unicode_ci,
  `payment` int(11) DEFAULT '0',
  `payment_group_id` int(11) DEFAULT NULL,
  `pickup_instructions` text COLLATE utf8_unicode_ci,
  `roaming_id` int(11) DEFAULT NULL,
  `region_id` smallint(6) DEFAULT NULL,
  `auto_allocation_lock` tinyint(1) DEFAULT '0',
  `extra_info` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `roaming_area_id` int(11) DEFAULT NULL,
  `pickupAt` int(11) DEFAULT NULL,
  `is_region_changed` int(11) DEFAULT '0',
  `test_column` int(11) DEFAULT '2',
  `is_broadcast` smallint(1) DEFAULT NULL,
  `store_pickup_latitude` float DEFAULT NULL,
  `store_pickup_longitude` float DEFAULT NULL,
  `store_pickup_phone` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `isPaymentAdjusted` int(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `roamer_uuid_index` (`uuid`),
  KEY `fk_roamer_request` (`roaming_id`),
  KEY `fk_roamer_request_roaming_area_id` (`roaming_area_id`),
  KEY `idx_store_id` (`store_id`),
  KEY `idx_driver_id` (`driver_id`),
  KEY `idx_status` (`status`),
  KEY `idx_status_store_id` (`status`,`store_id`),
  KEY `idx_archived_status_store_id_createdAt` (`isArchived`,`status`,`store_id`,`createdAt`),
  KEY `idx_is_broadcast` (`is_broadcast`),
  KEY `idx_run_id` (`run_id`),
  CONSTRAINT `fk_roamer_request` FOREIGN KEY (`roaming_id`) REFERENCES `roaming` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_roamer_request_roaming_area_id` FOREIGN KEY (`roaming_area_id`) REFERENCES `roaming_areas` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=99238 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `store` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `companyId` int(11) DEFAULT NULL,
  `businessTypeId` int(11) DEFAULT NULL,
  `paymentScheduleId` int(11) DEFAULT NULL,
  `imageId` int(11) DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `businessHours` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `storeProfile` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pickupNotes` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `createdAt` int(11) NOT NULL,
  `updatedAt` int(11) NOT NULL,
  `isArchived` tinyint(1) DEFAULT '0',
  `paymentsource` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mode` smallint(2) DEFAULT '0',
  `isBlocked` smallint(1) DEFAULT '0',
  `isRoamingEnabled` smallint(1) DEFAULT '0',
  `store_pickup_info` text COLLATE utf8_unicode_ci,
  `paymentMethod` varchar(25) COLLATE utf8_unicode_ci DEFAULT 'card',
  `signature_required` int(11) DEFAULT '0',
  `roamingStoreType` int(3) DEFAULT '0',
  `subscriptionPlanId` int(11) DEFAULT NULL,
  `accountManagerId` int(11) DEFAULT NULL,
  `salesPersonId` int(11) DEFAULT NULL,
  `createdById` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `store_businessTypeId` (`businessTypeId`),
  KEY `store_imageId` (`imageId`),
  KEY `store_companyId` (`companyId`),
  KEY `fk_store_has_subscription_plan_id` (`subscriptionPlanId`),
  KEY `idx-account_manager_id` (`accountManagerId`),
  KEY `idx-sales_person_id` (`salesPersonId`),
  FULLTEXT KEY `idx_store_title` (`title`),
  CONSTRAINT `store_businessTypeId` FOREIGN KEY (`businessTypeId`) REFERENCES `businesstype` (`id`),
  CONSTRAINT `store_companyId` FOREIGN KEY (`companyId`) REFERENCES `company` (`id`),
  CONSTRAINT `store_imageId` FOREIGN KEY (`imageId`) REFERENCES `image` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2833 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `store_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `value` text COLLATE utf8_unicode_ci NOT NULL,
  `store_id` int(11) NOT NULL,
  `createdAt` int(11) DEFAULT NULL,
  `updatedAt` int(11) DEFAULT NULL,
  `isArchived` smallint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `store_id` (`store_id`),
  CONSTRAINT `store_settings_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12199 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `roaming_area_setting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roaming_areas_id` int(11) DEFAULT NULL,
  `run_max_delivery_time` int(11) DEFAULT NULL,
  `run_max_orders` int(11) DEFAULT NULL,
  `bundle_frequency` int(11) DEFAULT NULL,
  `status` smallint(1) DEFAULT NULL,
  `createdAt` int(11) DEFAULT NULL,
  `updatedAt` int(11) DEFAULT NULL,
  `isArchived` smallint(1) DEFAULT '0',
  `pure_roaming_enabled` smallint(6) DEFAULT '0',
  `max_pickup_eta` int(11) DEFAULT '5400',
  `pure_roamer_priority_buffer` int(11) DEFAULT '0',
  `public_shift_roamer_priority_buffer` int(11) DEFAULT '0',
  `max_order_capacity` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_roaming_areas_setting_roaming_areas_id` (`roaming_areas_id`),
  CONSTRAINT `fk_roaming_areas_setting_roaming_areas_id` FOREIGN KEY (`roaming_areas_id`) REFERENCES `roaming_areas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `pickup_reassign` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roamerRequestId` int(11) DEFAULT NULL,
  `shiftId` int(11) DEFAULT NULL,
  `driverId` int(11) DEFAULT NULL,
  `type` varchar(32) DEFAULT NULL,
  `runId` varchar(36) DEFAULT NULL,
  `journeyId` varchar(72) DEFAULT NULL,
  `radiusToPickup` int(11) DEFAULT NULL,
  `expiresAt` int(11) DEFAULT NULL,
  `isActive` smallint(1) DEFAULT NULL,
  `errorData` varchar(1024) DEFAULT NULL,
  `createdAt` int(11) DEFAULT NULL,
  `updatedAt` int(11) DEFAULT NULL,
  `isArchived` smallint(1) DEFAULT '0',
  `driverLatitude` double DEFAULT NULL,
  `driverLongitude` double DEFAULT NULL,
  `message` varchar(128) DEFAULT NULL,
  `secondaryDriverId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pickup_reassign_roamer_request_roamerRequestId` (`roamerRequestId`),
  KEY `fk_pickup_reassign_shift_shiftId` (`shiftId`),
  KEY `fk_pickup_reassign_user_driverId` (`driverId`),
  KEY `fk_pickup_reassign_secondaryDriverId_user_id` (`secondaryDriverId`),
  KEY `fk_pickup_reassign_isArchived_journeyId` (`isArchived`,`journeyId`),
  CONSTRAINT `fk_pickup_reassign_roamer_request_roamerRequestId` FOREIGN KEY (`roamerRequestId`) REFERENCES `roamer_request` (`id`),
  CONSTRAINT `fk_pickup_reassign_secondaryDriverId_user_id` FOREIGN KEY (`secondaryDriverId`) REFERENCES `user` (`id`),
  CONSTRAINT `fk_pickup_reassign_shift_shiftId` FOREIGN KEY (`shiftId`) REFERENCES `shift` (`id`),
  CONSTRAINT `fk_pickup_reassign_user_driverId` FOREIGN KEY (`driverId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1255 DEFAULT CHARSET=latin1;
[6 Feb 2023 10:14] MySQL Verification Team 
Hello Ravi,

Thank you for the report and feedback.
Please note that MySQL 5.7.12 is nearly 7 year old release(2016-04-11, General Availability) and since then many bugs fixed, moreover we don't fix bugs in old versions, don't backport bug fixes, so you need to check with latest version anyway. So, please, upgrade to latest 5.7.41/8.0.32 and inform us if problem still exists with exact reproducible test case(logical dump of the tables along with subset of data and exact queries etc.).

If you can provide more information, feel free to add it to this bug(you may want to mark it as private after uploading requested data) and change the status back to 'Open'.  Thank you for your interest in MySQL.

regards,
Umesh
[7 Feb 2023 5:59] MySQL Verification Team 
Hello Ravi,

We recommend upgrading to 8.0.32.  MySQL 5.7 will be EOL this year (Oct 2023), so wasted effort to upgrade to version that will be EOL this year.  And, MySQL 5.6 has been EOL since Feb 2021(Ref https://www.mysql.com/support/eol-notice.html).  Please upgrade to latest MySQL 8.0.32. Thank you.

regards,
Umesh