Description

Compared to Commerce 4, we've noticed some big slowdowns when saving variants in a batch job that we use to import products.

This project has 650,000+ product/variants.

Below is the example code I've been using to profile the slowdown.

public function testSaveVariant(): void
{
    $variant = Variant::find()
        ->sku('my-sku')
        ->one();

    Craft::beginProfile('saveVariant');
    Craft::$app->elements->saveElement($variant, true, true, true);
    Craft::endProfile('saveVariant');
}

The slow query ends up being something like this.

UPDATE `craft_commerce_products`
SET
	`defaultVariantId` = 2023054,
	`defaultSku` = 'my-sku',
	`defaultPrice` = '0',
	`defaultHeight` = NULL,
	`defaultLength` = NULL,
	`defaultWidth` = NULL,
	`defaultWeight` = '0.0006',
	`dateUpdated` = '2025-06-26 17:52:10'
WHERE
	(`id` = 2023053)
	OR (`defaultVariantId` = 2023054)

If I turn that into a select & analyze it, it seems like defaultVariantId doesn't hit any sort of index.

SELECT
	`id`,
	`defaultVariantId`,
	`defaultSku`,
	`defaultPrice`,
	`defaultHeight`,
	`defaultLength`,
	`defaultWidth`,
	`defaultWeight`,
	`dateUpdated`
FROM
	`craft_commerce_products`
WHERE
	(`id` = 2023053)
	OR (`defaultVariantId` = 2023054)

Adding the following index takes the SELECT/UPDATE queries down to around 1-2 ms

Craft CMS version

5.7.4

Craft Commerce version

5.4.0

PHP version

8.3

Operating system and version

DDEV

Database type and version

MySQL 8.0.40

Image driver and version

No response

Installed plugins and versions