Adobe Magento Commerce EAV performance issue. FIX included

EAV Adobe Commerce Performance issue

Default Magento 2 EAV SQL statment:

explain SELECT `u`.* FROM ( (SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_varchar` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN (‘60’,
‘219’, ‘368’, ‘255’, ‘266’, ‘74’, ‘71’, ‘456’, ‘355’, ‘92’, ‘188’, ‘220’, ‘147’, ‘367’, ‘253’, ‘267’, ‘75’, ‘424’, ‘362’, ‘467’, ‘246’, ‘235’, ‘263’, ‘268’, ‘76’, ‘73’, ‘442’, ‘363’, ‘321’, ‘236’, ‘351’, ‘269’, ‘77’, ‘447’, ‘364’, ‘337’, ‘417’, ‘86’, ‘238’, ‘259’, ‘469’, ‘449’, ‘506’, ‘96’, ‘373’, ‘244’, ‘233’, ‘264’, ‘258’, ‘83’, ‘450’, ‘507’, ‘98’, ‘155’, ‘265’, ‘354’, ‘194’, ‘451’, ‘508’, ‘239’, ‘350’, ‘216’, ‘448’, ‘510’, ‘270’, ‘232’, ‘240’, ‘425’, ‘511’, ‘320’, ‘359’, ‘241’, ‘443’, ‘509’, ‘87’, ‘391’, ‘444’, ‘365’, ‘392’, ‘243’, ‘455’, ‘414’, ‘250’, ‘454’, ‘279’, ‘361’, ‘248’, ‘446’, ‘141’, ‘251’, ‘453’, ‘101’, ‘486’, ‘252’, ‘102’, ‘281’, ‘237’, ‘457’, ‘103’, ‘397’, ‘261’, ‘458’, ‘452’, ‘254’, ‘432’, ‘260’, ‘262’, ‘372’, ‘288’, ‘496’, ‘357’, ‘513’, ‘130’, ‘111’, ‘118’, ‘514’, ‘119’, ‘515’, ‘516’, ‘517’, ‘520’, ‘525’, ‘389’, ‘387’, ‘420’, ‘421’, ‘422’, ‘497’)) AND (`store_id` IN (‘1’, 0)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_text` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN (‘61’, ‘143’, ‘159’, ‘62’, ‘95’, ‘160’, ‘72’, ‘144’, ‘140’, ‘90’, ‘217’, ‘189’, ‘419’)) AND (`store_id` IN (‘1’, 0)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_decimal` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN
(‘416’, ‘64’, ‘65’, ‘79’, ‘330’, ‘88’, ‘329’, ‘69’, ‘68’, ‘161’, ‘123’, ‘124’, ‘282’)) AND (`store_id` IN (‘1’, 0)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_int` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN (‘116’, ‘181’, ‘179’, ‘167’, ‘287’, ‘182’, ‘211’, ‘183’, ‘212’, ‘171’, ‘78’, ‘145’, ‘175’, ‘229’, ‘505’, ‘84’, ‘170’, ‘164’, ‘91’, ‘85’, ‘502’, ‘352’, ‘115’, ‘466’, ‘228’, ‘222’, ‘245’, ‘434’, ‘353’, ‘148’, ‘223’, ‘156’, ‘89’, ‘335’, ‘112’, ‘224’, ‘230’, ‘504’, ‘113’, ‘225’, ‘126’, ‘284’, ‘114’, ‘127’, ‘226’, ‘234’, ‘128’, ‘331’, ‘231’, ‘129’, ‘378’, ‘334’, ‘379’, ‘333’, ‘117’, ‘519’, ‘131’, ‘380’, ‘132’, ‘381’, ‘490’, ‘133’, ‘382’, ‘120’, ‘383’, ‘384’, ‘385’)) AND (`store_id` IN (‘1’, 0)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_datetime` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN (‘162’, ‘93’, ‘66’, ‘94’, ‘67’, ‘81’, ‘82’, ‘499’, ‘501’)) AND (`store_id` IN (‘1’, 0))) ) AS `u` ORDER BY `store_id` ASC \G

Expalin statement result:

  • ************************ 1. row ***************************
    id: 1
    select_type: PRIMARY
    table: <derived2>
  • rows: 256
    filtered: 100.00
    Extra: Using index condition

Derived Tables in SQL Server

Fix of this EAV SQL Query

Optimized Magento 2 EAV SQL statement:

explain (SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_varchar` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN (‘60’, ‘219’, ‘368’, ‘255’, ‘266’, ‘74’, ‘71’, ‘456’, ‘355’, ‘92’, ‘188’, ‘220’, ‘147’, ‘367’, ‘253’, ‘267’, ‘75’, ‘424’, ‘362’, ‘467’, ‘246’, ‘235’, ‘263’, ‘268’, ‘76’, ‘73’, ‘442’, ‘363’, ‘321’, ‘236’, ‘351’, ‘269’,
‘77’, ‘447’, ‘364’, ‘337’, ‘417’, ‘86’, ‘238’, ‘259’, ‘469’, ‘449’, ‘506’, ‘96’, ‘373’, ‘244’, ‘233’, ‘264’, ‘258’, ‘83’, ‘450’, ‘507’, ‘98’, ‘155’, ‘265’, ‘354’, ‘194’, ‘451’, ‘508’, ‘239’,
‘350’, ‘216’, ‘448’, ‘510’, ‘270’, ‘232’, ‘240’, ‘425’, ‘511’, ‘320’, ‘359’, ‘241’, ‘443’, ‘509’, ‘87’, ‘391’, ‘444’, ‘365’, ‘392’, ‘243’, ‘455’, ‘414’, ‘250’, ‘454’, ‘279’, ‘361’, ‘248’, ‘446’, ‘141’, ‘251’, ‘453’, ‘101’, ‘486’, ‘252’, ‘102’, ‘281’, ‘237’, ‘457’, ‘103’, ‘397’, ‘261’, ‘458’, ‘452’, ‘254’, ‘432’, ‘260’, ‘262’, ‘372’, ‘288’, ‘496’, ‘357’, ‘513’, ‘130’, ‘111’, ‘118’, ‘514’, ‘119’, ‘515’, ‘516’, ‘517’, ‘520’, ‘525’, ‘389’, ‘387’, ‘420’, ‘421’, ‘422’, ‘497’)) AND (`store_id` IN (‘1’, 0)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_text` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN (‘61’, ‘143’, ‘159’, ‘62’, ‘95’, ‘160’, ‘72’, ‘144’, ‘140’, ‘90’, ‘217’, ‘189’, ‘419’)) AND (`store_id`
IN (‘1’, 0)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_decimal` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN (‘416’, ‘64’, ‘65’,
‘79’, ‘330’, ‘88’, ‘329’, ‘69’, ‘68’, ‘161’, ‘123’, ‘124’, ‘282’)) AND (`store_id` IN (‘1’, 0)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_int` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN (‘116’, ‘181’, ‘179’, ‘167’, ‘287’, ‘182’, ‘211’, ‘183’, ‘212’, ‘171’, ‘78’, ‘145’, ‘175’, ‘229’, ‘505’, ‘84’, ‘170’, ‘164’, ‘91’, ‘85’, ‘502’, ‘352’, ‘115’, ‘466’, ‘228’, ‘222’, ‘245’, ‘434’, ‘353’, ‘148’, ‘223’, ‘156’, ‘89’, ‘335’, ‘112’, ‘224’, ‘230’, ‘504’, ‘113’, ‘225’, ‘126’, ‘284’, ‘114’, ‘127’, ‘226’, ‘234’, ‘128’, ‘331’, ‘231’, ‘129’, ‘378’, ‘334’, ‘379’, ‘333’, ‘117’, ‘519’, ‘131’, ‘380’, ‘132’, ‘381’, ‘490’, ‘133’, ‘382’, ‘120’, ‘383’, ‘384’, ‘385’)) AND (`store_id` IN (‘1’, 0)))UNION ALL(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id` FROM `catalog_product_entity_datetime` AS `t` WHERE (entity_id = ‘9244’) AND (attribute_id IN (‘162’, ‘93’, ‘66’, ‘94’, ‘67’, ‘81’, ‘82’, ‘499’,
‘501’)) AND (`store_id` IN (‘1’, 0))) \G

Expalin statement result:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Yegor Shytikov

Yegor Shytikov

True Stories about Magento 2. Melting metal server infrastructure into cloud solutions. Donate: https://paypal.me/magentasoftware?country.x=US&locale.x=en_US