[5.x]: Product index unnecessarily eager loads all variants for defaultPrice/defaultSku columns #4236
Description
Description
The product listing page in the CP has gotten slower on Commerce sites that are variant-heavy. When loading the product index with default table columns, Commerce appears to eager load every single variant for every displayed product, even though the column values being displayed (defaultPrice, defaultSku, etc.) are already fetched by the main product query through direct SQL JOINs. The result is a redundant query that's heavy, and comes back with a heavy payload.
Under the hood, Product::prepElementQueryForTableAttribute() calls $elementQuery->andWith('variants') whenever any variant-related column is visible in the table. But ProductQuery::beforePrepare() already selects defaultPrice, defaultSku, defaultWeight, etc. directly from the purchasables and purchasables_stores tables.
From my understanding the only columns that genuinely need eager-loaded variant elements are variants (which renders variant chips) and stock (which sums inventory across variants).
On our site (600 variants across 100 displayed products out of 1k+ products total), this single query accounts for 4s out of a 5s page load on the product index. This happens on every page. The workaround listed below dropped the request duration by 95%, from ~5000ms down to to 250ms.
Steps to reproduce
- Have a Commerce store with a decent number of products/variants (ours has 600 variants across 1K products)
- Open the Products index in the CP with default table columns (defaultPrice, defaultSku visible)
- Profile the get-elements AJAX request via debug bar
- Observe a slow query loading all variants via the elements/commerce_variants/elements_owners tables with a large IN (...) clause
Expected behavior
The product index should load well in under 1s for reasonably specced servers. Columns like defaultPrice and defaultSku shouldn't trigger variant eager loading. Query should only take longer if you add Variants to the table columns.
Actual behavior
A lengthy ~4s query eager loads every variant element for all displayed products, even though the data for defaultPrice/defaultSku/etc. is already on the product model from the main query. Same query duration with or without Variants or Stock in the table is a good indicator.
Workaround
Register an event listener on EVENT_PREP_QUERY_FOR_TABLE_ATTRIBUTE to prevent the eager load for attributes that are already available from the main query:
use craft\base\Element;
use craft\commerce\elements\Product;
use craft\events\ElementIndexTableAttributeEvent;
Event::on(
Product::class,
Element::EVENT_PREP_QUERY_FOR_TABLE_ATTRIBUTE,
function (ElementIndexTableAttributeEvent $e) {
$skipEagerLoad = [
'defaultPrice',
'defaultPromotionalPrice',
'defaultSku',
'defaultWeight',
'defaultLength',
'defaultWidth',
'defaultHeight',
];
if (in_array($e->attribute, $skipEagerLoad, true)) {
$e->handled = true;
}
}
);
Craft CMS version
5.9.10
Craft Commerce version
5.5.3
PHP version
8.3
Operating system and version
No response
Database type and version
MySQL 8.0
Image driver and version
No response