Adobe Magento Commerce EAV performance issue. FIX included

Adobe Commerce's outdated EAV architecture has a huge performance issue. In this post, we will check and fix a single SQL query issue in the Adobe core.

Magento EAV SQL queries are a top request for the database for high-traffic websites that can easily kill database performance.

EAV Adobe Commerce Performance issue

Adobe Commerce is just rebranded Magento 2 with all inherited performance issues.

Adobe Commerce has a next SQL query to Product EAV attribute data.

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>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 359
filtered: 100.00
Extra: Using filesort
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID
key: CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 256
filtered: 100.00
Extra: Using index condition
*************************** 3. row ***************************
id: 3
select_type: UNION
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_TEXT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID
key: CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 26
filtered: 100.00
Extra: Using index condition
*************************** 4. row ***************************
id: 4
select_type: UNION
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID
key: CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 26
filtered: 100.00
Extra: Using index condition
*************************** 5. row ***************************
id: 5
select_type: UNION
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID
key: CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 134
filtered: 100.00
Extra: Using index condition
*************************** 6. row ***************************
id: 6
select_type: UNION
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID
key: CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 18
filtered: 100.00
Extra: Using index condition
6 rows in set, 1 warning (0.00 sec)

We can see redundant stupid step 1.

Magento 2 selects all EAV attributes and saves that data to derived2 table to select after and sort. So, MySQL needs to select twice in this case when getting data from the table and a second time when selecting and sorting that data from the temporary table again. In the Magento, each attribute is a row in the table, so the cyclomatic time complexity of the EAV Adobe Commerce SQL query equal to O(N) where N numbers of attributes.

This second select and sort don’t have any sense. Anyway, it will be processed in PHP after. So less redundant operation you are doing in MySQL less load MySQL has.

Becouse of the broken Adobe Comerce architecture merchants has huge performance issues for the big or small catalogs which has thousands attribute per product to render specification.

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

Derived Tables in SQL Server

A derived table is an example of a subquery that is used in the FROM clause of a SELECT statement to retrieve a set of records. You can use derived tables to break a complex query into separate logical steps, and they are often a neat alternative to using temporary tables.

A filesort operation uses temporary disk files as necessary if the result set is too large to fit in memory. Some types of queries are particularly suited to completely in-memory filesort operations. For example, the optimizer can use filesort to efficiently handle in memory, without temporary files.

filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is quicksort at heart.

If the sort is bigger than the sort buffer, it is performed a bit at a time, and then the chunks are merge-sorted to produce the final sorted output.

If a filesort is performed, optimizer trace output includes a filesort_summary block. peak_memory_used indicates the maximum memory used at any one time during the sort. This is a value up to but not necessarily as large as the value of the sort_buffer_size system variable.

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:

*********************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID
key: CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 256
filtered: 100.00
Extra: Using index condition
*************************** 2. row ***************************
id: 2
select_type: UNION
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_TEXT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID
key: CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 26
filtered: 100.00
Extra: Using index condition
*************************** 3. row ***************************
id: 3
select_type: UNION
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID
key: CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 26
filtered: 100.00
Extra: Using index condition
*************************** 4. row ***************************
id: 4
select_type: UNION
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID
key: CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 134
filtered: 100.00
Extra: Using index condition
*************************** 5. row ***************************
id: 5
select_type: UNION
table: t
partitions: NULL
type: range
possible_keys: CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID
key: CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
key_len: 8
ref: NULL
rows: 18
filtered: 100.00
Extra: Using index condition
5 rows in set, 1 warning (0.01 sec)

As we can see this fixed EAV query is as low as 2 times faster.

Avoid derived tables — If there is another way to write the query, it will be faster in most cases. In many cases, even a separate temporary table will be faster as you can add proper indexes to the table in this case.

Adobe Commerce code profiling showing a good image of this performance issue. A single SQL query execution time is 110ms. Just terrible for the enterprise platform.

We have improved just a single query, but Adobe Commerce has much more issues. Now you can know the truth about Adobe EAV implementation. So when a salesperson lies to you about good Adobe Commerce performance, you are prepared.