Mysql 8.0.23 is causing heavy dip in performance for the select queries

Question

I have a simple select query which earlier in mysql 5.7 version used to work in 850 ms is now taking 2.20 seconds to run.

SELECT c.id,
c.cart_type as cartType,
c.name,
c.storefront_id as storefrontId,
c.contact_id as contactId,
c.account_number as accountNumber,
c.ship_to as shipTo,
c.name1 as shipToName1,
c.name2 as shipToName2,
c.city,
c.country,
c.status,
c.order_type as orderType,
c.currency as currency,
c.updated_at as updatedOn,
CASE WHEN EXISTS (SELECT 1
FROM contact_active_cart cac
WHERE cac.cart_id = c.id) THEN 'true' ELSE 'false' END as isActive
FROM (SELECT id
FROM cart c
WHERE c.cart_type = 'cart'
AND c.storefront_id = 1
AND c.status IN (
7, 8, 6, 2, 0
)
AND c.account_number in (
'0100000106', '0100000212', '0100000806', '0100000860', '0100001329', '0100001537', '0100010879',     '0100012479', '0100012553', '0100012579'
        )
ORDER BY created_at ASC
LIMIT 0,
200 ) as cart_inner_subselect
INNER JOIN cart c ON cart_inner_subselect.id = c.id;

Adding create table on request:

CREATE TABLE `cart` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `contact_id` varchar(255) NOT NULL,
  `storefront_id` bigint NOT NULL,
  `cart_type` varchar(50) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  `ship_to` varchar(255) NOT NULL,
  `account_number` varchar(40) DEFAULT NULL,
   `name1` varchar(35) DEFAULT NULL,
  `name2` varchar(35) DEFAULT NULL, `city` varchar(35) DEFAULT NULL,
  `state` varchar(80) DEFAULT NULL,
  `country` varchar(80) DEFAULT NULL,
  `order_type` varchar(5) DEFAULT NULL,
  `currency` varchar(3) DEFAULT NULL
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk-cart` (`contact_id`,`storefront_id`,`name`,`cart_type`,`submission_datetime`),
  KEY `idx-cart-cart_type` (`cart_type`),
  KEY `idx-cart-cart_overview_1` (`cart_type`,`storefront_id`,`status`,`account_number`,`created_at`),
  KEY `idx-cart-cart_overview_contact` (`cart_type`,`storefront_id`,`status`,`account_number`,`contact_id`,`created_at`),
  KEY `idx-cart-cart_overview_shipTo` (`cart_type`,`storefront_id`,`status`,`account_number`,`ship_to`,`created_at`),
  KEY `idx-cart-cart_overview_all` (`cart_type`,`storefront_id`,`status`,`account_number`,`contact_id`,`ship_to`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=24331499 DEFAULT CHARSET=utf8mb3;

CREATE TABLE `contact_active_cart` (
  `contact_id` varchar(255) NOT NULL,
  `cart_id` bigint NOT NULL,
  `storefront_id` bigint NOT NULL,
  PRIMARY KEY (`contact_id`,`storefront_id`),
  KEY `idx-contact_active_cart-cart_id` (`cart_id`),
  KEY `idx-contact_active_cart-contact_id` (`contact_id`),
  KEY `idx-contact_active_cart-storefront_id` (`storefront_id`),
  CONSTRAINT `contact_active_cart_FK` FOREIGN KEY (`cart_id`) REFERENCES `cart` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

I have added an index on cart_type, storefront_id, status, account_number. So, when i used to run this on version 5.7 the index was being picked up correctly while in 8.0 it is not picking up and the time taken is way higher than previously.

The data in say the cart table is around 600M. Please suggest any improvements or should i upgrade to any higher version in order to see any performance improvement.

In addition to the ask for checking the explain: I tried adding created_at in the index i was using (index idx-cart-cart_overview_1). it reduced the time but still the time taken is higher than what i was getting in version 5.7. Also, if i add any additional filters in the where clause say contact_id or ship_to it is totally ignoring the indexes that should be used and refers idx-cart-cart_type using and the response time increases

Answer 1

In really can't see why you need the subquuery, it is superfluous

As you can see the query becomes instant quicker.

The number of indexes seems on the first glance excessive, you should see with other explains if they are all needed.

CREATE TABLE `cart` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `contact_id` varchar(255) NOT NULL,
  `storefront_id` bigint NOT NULL,
  `cart_type` varchar(50) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL,
  `ship_to` varchar(255) NOT NULL,
  `account_number` varchar(40) DEFAULT NULL,
   `name1` varchar(35) DEFAULT NULL,
  `name2` varchar(35) DEFAULT NULL, `city` varchar(35) DEFAULT NULL,
  `state` varchar(80) DEFAULT NULL,
  `country` varchar(80) DEFAULT NULL,
  `order_type` varchar(5) DEFAULT NULL,
  `currency` varchar(3) DEFAULT NULL,
  `submission_datetime` datetime,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk-cart` (`contact_id`,`storefront_id`,`name`,`cart_type`,`submission_datetime`),
  KEY `idx-cart-cart_type` (`cart_type`),
  KEY `idx-cart-cart_overview_1` (`cart_type`,`storefront_id`,`status`,`account_number`,`created_at`),
  KEY `idx-cart-cart_overview_contact` (`cart_type`,`storefront_id`,`status`,`account_number`,`contact_id`,`created_at`),
  KEY `idx-cart-cart_overview_shipTo` (`cart_type`,`storefront_id`,`status`,`account_number`,`ship_to`,`created_at`),
  KEY `idx-cart-cart_overview_all` (`cart_type`,`storefront_id`,`status`,`account_number`,`contact_id`,`ship_to`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=24331499 DEFAULT CHARSET=utf8mb3;

CREATE TABLE `contact_active_cart` (
  `contact_id` varchar(255) NOT NULL,
  `cart_id` bigint NOT NULL,
  `storefront_id` bigint NOT NULL,
  PRIMARY KEY (`contact_id`,`storefront_id`),
  KEY `idx-contact_active_cart-cart_id` (`cart_id`),
  KEY `idx-contact_active_cart-contact_id` (`contact_id`),
  KEY `idx-contact_active_cart-storefront_id` (`storefront_id`),
  CONSTRAINT `contact_active_cart_FK` FOREIGN KEY (`cart_id`) REFERENCES `cart` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

EXPLAIN SELECT c.id,
c.cart_type as cartType,
c.name,
c.storefront_id as storefrontId,
c.contact_id as contactId,
c.account_number as accountNumber,
c.ship_to as shipTo,
c.name1 as shipToName1,
c.name2 as shipToName2,
c.city,
c.country,
c.status,
c.order_type as orderType,
c.currency as currency,
c.updated_at as updatedOn,
CASE WHEN EXISTS (SELECT 1
FROM contact_active_cart cac
WHERE cac.cart_id = c.id) THEN 'true' ELSE 'false' END as isActive
FROM (SELECT id
FROM cart c
WHERE c.cart_type = 'cart'
AND c.storefront_id = 1
AND c.status IN (
7, 8, 6, 2, 0
)
AND c.account_number in (
'0100000106', '0100000212', '0100000806', '0100000860', '0100001329', '0100001537', '0100010879',     '0100012479', '0100012553', '0100012579'
        )
ORDER BY created_at ASC
LIMIT 0,
200 ) as cart_inner_subselect
INNER JOIN cart c ON cart_inner_subselect.id = c.id;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	c	null	ALL	PRIMARY	null	null	null	1	100.00	null
1	PRIMARY	<derived3>	null	ref	<auto_key0>	<auto_key0>	8	fiddle.c.id	2	100.00	Using index
3	DERIVED	c	null	ref	idx-cart-cart_type,idx-cart-cart_overview_1,idx-cart-cart_overview_contact,idx-cart-cart_overview_shipTo,idx-cart-cart_overview_all	idx-cart-cart_type	153	const	1	100.00	Using where; Using filesort
2	DEPENDENT SUBQUERY	cac	null	ref	idx-contact_active_cart-cart_id	idx-contact_active_cart-cart_id	8	fiddle.c.id	1	100.00	Using index

EXPLAIN SELECT 
    c.id,
    c.cart_type AS cartType,
    c.name,
    c.storefront_id AS storefrontId,
    c.contact_id AS contactId,
    c.account_number AS accountNumber,
    c.ship_to AS shipTo,
    c.name1 AS shipToName1,
    c.name2 AS shipToName2,
    c.city,
    c.country,
    c.status,
    c.order_type AS orderType,
    c.currency AS currency,
    c.updated_at AS updatedOn,
    CASE
        WHEN
            EXISTS( SELECT 
                    1
                FROM
                    contact_active_cart cac
                WHERE
                    cac.cart_id = c.id)
        THEN
            'true'
        ELSE 'false'
    END AS isActive
FROM
        cart c
    WHERE
        c.cart_type = 'cart'
            AND c.storefront_id = 1
            AND c.status IN (7 , 8, 6, 2, 0)
            AND c.account_number IN ('0100000106' , '0100000212', '0100000806', '0100000860', '0100001329', '0100001537', '0100010879', '0100012479', '0100012553', '0100012579')
    ORDER BY created_at ASC
    LIMIT 0 , 200;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	c	null	ref	idx-cart-cart_type,idx-cart-cart_overview_1,idx-cart-cart_overview_contact,idx-cart-cart_overview_shipTo,idx-cart-cart_overview_all	idx-cart-cart_type	153	const	1	100.00	Using where; Using filesort
2	DEPENDENT SUBQUERY	cac	null	ref	idx-contact_active_cart-cart_id	idx-contact_active_cart-cart_id	8	fiddle.c.id	1	100.00	Using index

fiddle