Product attribute Web API endpoints run one eav_attribute_option query per select/multiselect attribute when serializing options #41729

Preconditions and environment

• Magento version: 2.4-develop
• PHP 8.4, MariaDB 11.4, clean install, no third-party modules
• An admin Bearer token for the REST API

Steps to reproduce

1. Get an admin token:

curl -sk https://<host>/rest/V1/integration/admin/token -H 'Content-Type: application/json' -d '{"username":"<admin>","password":"<password>"}'

2. Create 10 dropdown attributes with 3 options each (repeat for i = 1..10):

curl -sk -X POST https://<host>/rest/V1/products/attributes -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' -d '{"attribute":{"attribute_code":"test_select_'$i'","frontend_input":"select","default_frontend_label":"Test select '$i'","is_required":false,"options":[{"label":"a"},{"label":"b"},{"label":"c"}]}}'

3. Create an attribute set based on Default (id 4) and note its id ($SET) and the id of its first group ($GROUP, e.g. from eav_attribute_group):

curl -sk -X POST https://<host>/rest/V1/products/attribute-sets -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' -d '{"attributeSet":{"attribute_set_name":"test-set","entity_type_id":4},"skeletonId":4}'

4. Enable the general query log (SET GLOBAL general_log=1), request GET /rest/V1/products/attribute-sets/$SET/attributes (after two warm-up requests) and count the eav_attribute_option queries. Repeat after assigning 2 and then all 10 of the new attributes to the set:

curl -sk -X POST https://<host>/rest/V1/products/attribute-sets/attributes -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' -d '{"attributeSetId":'$SET',"attributeGroupId":'$GROUP',"attributeCode":"test_select_'$i'","sortOrder":'$((100+i))'}'
curl -sk -H "Authorization: Bearer $TOKEN" https://<host>/rest/V1/products/attribute-sets/$SET/attributes

5. Do the same for GET /rest/V1/products/attributes?searchCriteria[pageSize]=200 before step 2 and after step 2.

Expected result

The options of all attributes in the response are loaded with a constant number of queries (for example one query for all option-bearing attributes), so the query count does not grow with the number of select/multiselect attributes.

Actual result

Each select/multiselect attribute that uses the default Magento\Eav\Model\Entity\Attribute\Source\Table source model adds one query of this shape, with only the attribute id changing:

SELECT `main_table`.*, `tdv`.`value` AS `default_value`, `tsv`.`value` AS `store_default_value`, IF(tsv.value_id > 0, tsv.value, tdv.value) AS `value` FROM `eav_attribute_option` AS `main_table` INNER JOIN `eav_attribute_option_value` AS `tdv` ON tdv.option_id = main_table.option_id LEFT JOIN `eav_attribute_option_value` AS `tsv` ON tsv.option_id = main_table.option_id AND tsv.store_id = '1' WHERE (`main_table`.`attribute_id` = '<id>') AND (tdv.store_id = 0) ORDER BY main_table.sort_order ASC, `value` ASC

Measured on a clean install (warm cache, HTTP 200 in every case):

A catalog with a few hundred dropdown attributes, which is common, issues a few hundred option queries for every call of these endpoints.

Additional information

Magento\Eav\Model\AttributeManagement::getAttributes() loads the attribute collection of the set and returns its items:

During Web API output serialization Magento\Eav\Api\Data\AttributeInterface::getOptions() is read for each item. Magento\Eav\Model\Entity\Attribute\AbstractAttribute::getOptions() falls back to getSource()->getAllOptions() when no options were set on the model:

Magento\Eav\Model\Entity\Attribute\Source\Table::getAllOptions() caches options only per source instance, store and attribute id, so every attribute triggers its own option collection load:

GET /V1/products/attributes goes through Magento\Catalog\Model\Product\Attribute\Repository::getList() and Magento\Eav\Model\AttributeRepository::getList(), which load the collection and then call get() for every attribute. The serializer ends up in the same Table::getAllOptions() path. That endpoint also issues per-attribute catalog_eav_attribute and eav_attribute lookups (16 and 10 queries respectively in the 10-attribute run above), but this report is only about the option queries.

This is a different path from #36174 / #38934 (and PRs #40934 / #39483), which cover Table::getSpecificOptions() being called repeatedly for the same attribute. Those changes do not batch getAllOptions() across attributes, so the counts above would not change.

A possible direction: after loading the attribute collection, preload options for all option-bearing attributes that use the Table source in one eav_attribute_option query (filtered by attribute_id IN (...) and the store) and set them on each attribute, or seed the Table source cache from that one query.

Release note

Product attribute Web API endpoints load select/multiselect attribute options with a constant number of queries instead of one query per attribute.

Triage and priority

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “user experience”.

_{Identified from production performance monitoring (New Relic) on Magento stores maintained by Fast White Cat. The root cause was verified against a clean 2.4-develop; no store data is included in this report.}