[5.x]: Catalog pricing slows product query down enormously #4029
Description
What happened?
Description
After our upgrade to Craft 5, the site began performing extremely slowly - to the point of being unusable. We traced the issue to the query that selects a single product. The subquery that incorporates catalog pricing increased the execution time of the product query to an average of 1.831 seconds. That number is just execution time and doesn't include transfer time or connection time, but I'd expect those numbers to be about the same across Craft Commerce 4 and Craft Commerce 5. I don't have timing on the query from Craft Commerce 4 since we never encountered a performance issue with it.
Note that this issue is similar to #3707, but we are not using MariaDB. We are using MySQL.
Here is the complete query to retrieve one product (formatted for readability):
SELECT `elements`.`id`,
`elements`.`canonicalId`,
`elements`.`fieldLayoutId`,
`elements`.`uid`,
`elements`.`enabled`,
`elements`.`archived`,
`elements`.`dateLastMerged`,
`elements`.`dateCreated`,
`elements`.`dateUpdated`,
`elements_sites`.`id` AS `siteSettingsId`,
`elements_sites`.`siteId`,
`elements_sites`.`title`,
`elements_sites`.`slug`,
`elements_sites`.`uri`,
`elements_sites`.`content`,
`elements_sites`.`enabled` AS `enabledForSite`,
`commerce_products`.`id`,
`commerce_products`.`typeId`,
`commerce_products`.`postDate`,
`commerce_products`.`expiryDate`,
`subquery`.`price` AS `defaultPrice`,
`purchasablesstores`.`basePrice` AS `defaultBasePrice`,
`commerce_products`.`defaultVariantId`,
`purchasables`.`sku` AS `defaultSku`,
`purchasables`.`weight` AS `defaultWeight`,
`purchasables`.`length` AS `defaultLength`,
`purchasables`.`width` AS `defaultWidth`,
`purchasables`.`height` AS `defaultHeight`,
`sitestores`.`storeId`,
`structureelements`.`root`,
`structureelements`.`lft`,
`structureelements`.`rgt`,
`structureelements`.`level`,
`structureelements`.`structureId`
FROM (SELECT `catalogprices`.`price`,
`elements`.`id` AS `elementsId`,
`elements_sites`.`id` AS `siteSettingsId`,
`structureelements`.`structureId`
FROM `craft_elements` `elements`
INNER JOIN `craft_commerce_products` `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
LEFT JOIN `craft_structureelements` `structureelements`
ON (`structureelements`.`elementId` = `elements`.`id`) AND (EXISTS (SELECT *
FROM `craft_structures` use index (primary)
WHERE (`id` = `structureelements`.`structureId`)
AND (`dateDeleted` IS NULL)))
LEFT JOIN `craft_commerce_site_stores` `sitestores` ON `elements_sites`.`siteId` = `sitestores`.`siteId`
LEFT JOIN (SELECT MIN(CASE WHEN `isPromotionalPrice` = FALSE THEN `price` END) AS `price`,
MIN(CASE WHEN `isPromotionalPrice` = TRUE THEN `price` END) AS `promotionalPrice`,
MIN(`price`) AS `salePrice`,
`cp`.`purchasableId`,
`cp`.`storeId`
FROM `craft_commerce_catalogpricing` `cp`
LEFT JOIN `craft_commerce_variants` `purvariants`
ON `purvariants`.`id` = `cp`.`purchasableId`
WHERE ((`catalogPricingRuleId` IS NULL) OR
(`catalogPricingRuleId` IN (SELECT `cpr`.`id` AS `cprid`
FROM `craft_commerce_catalogpricingrules` `cpr`
LEFT JOIN `craft_commerce_catalogpricingrules_users` `cpru`
ON `cpr`.`id` = `cpru`.`catalogPricingRuleId`
WHERE `cpru`.`id` IS NULL
GROUP BY `cpr`.`id`)))
AND ((`dateFrom` IS NULL) OR (`dateFrom` <= '2025-05-24 15:06:01'))
AND ((`dateTo` IS NULL) OR (`dateTo` >= '2025-05-24 15:06:01'))
AND (`purvariants`.`isDefault` = 1)
GROUP BY `purchasableId`, `storeId`) `catalogprices`
ON `catalogprices`.`purchasableId` = `commerce_products`.`defaultVariantId` AND
`catalogprices`.`storeId` = `sitestores`.`storeId`
WHERE (`commerce_products`.`defaultSku` = 'LATLFE5U-25F-6BG256C')
AND (`elements_sites`.`siteId` = 5)
AND (((`elements`.`enabled` = 1) AND (`elements_sites`.`enabled` = 1)) AND
(`commerce_products`.`postDate` <= '2025-05-24 15:06:01') AND
((`commerce_products`.`expiryDate` IS NULL) OR (`commerce_products`.`expiryDate` > '2025-05-24 15:06:01')))
AND (`elements`.`archived` = 0)
AND (`elements`.`dateDeleted` IS NULL)
AND (`elements`.`draftId` IS NULL)
AND (`elements`.`revisionId` IS NULL)
ORDER BY `structureelements`.`lft`, `commerce_products`.`postDate` DESC, `elements`.`id` DESC
LIMIT 1) `subquery`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`siteSettingsId`
INNER JOIN `craft_commerce_products` `commerce_products` ON `commerce_products`.`id` = `subquery`.`elementsId`
LEFT JOIN `craft_commerce_site_stores` `sitestores` ON `elements_sites`.`siteId` = `sitestores`.`siteId`
LEFT JOIN `craft_commerce_purchasables` `purchasables`
ON `purchasables`.`id` = `commerce_products`.`defaultVariantId`
LEFT JOIN `craft_commerce_purchasables_stores` `purchasablesstores`
ON `purchasablesstores`.`purchasableId` = `commerce_products`.`defaultVariantId` and
`sitestores`.`storeId` = `purchasablesstores`.`storeId`
LEFT JOIN `craft_structureelements` `structureelements`
ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND
(`structureelements`.`structureId` = `subquery`.`structureId`)
ORDER BY `structureelements`.`lft`, `commerce_products`.`postDate` DESC, `elements`.`id` DESC;After doing some analysis on the query, it looks like the biggest offender for the performance hit is the subquery named catalogprices that starts on line 48. It looks like this:
SELECT MIN(CASE WHEN `isPromotionalPrice` = FALSE THEN `price` END) AS `price`,
MIN(CASE WHEN `isPromotionalPrice` = TRUE THEN `price` END) AS `promotionalPrice`,
MIN(`price`) AS `salePrice`,
`cp`.`purchasableId`,
`cp`.`storeId`
FROM `craft_commerce_catalogpricing` `cp`
LEFT JOIN `craft_commerce_variants` `purvariants`
ON `purvariants`.`id` = `cp`.`purchasableId`
WHERE ((`catalogPricingRuleId` IS NULL) OR
(`catalogPricingRuleId` IN (SELECT `cpr`.`id` AS `cprid`
FROM `craft_commerce_catalogpricingrules` `cpr`
LEFT JOIN `craft_commerce_catalogpricingrules_users` `cpru`
ON `cpr`.`id` = `cpru`.`catalogPricingRuleId`
WHERE `cpru`.`id` IS NULL
GROUP BY `cpr`.`id`)))
AND ((`dateFrom` IS NULL) OR (`dateFrom` <= '2025-05-24 15:06:01'))
AND ((`dateTo` IS NULL) OR (`dateTo` >= '2025-05-24 15:06:01'))
AND (`purvariants`.`isDefault` = 1)
GROUP BY `purchasableId`, `storeId`;I think the root of the issue is that the subquery materializes a table equal to the size of the craft_commerce_variants table, which can get large for large stores (like ours). The only filter on that table is the purvariants.isDefault = 1 criteria. We don't use variants, so that doesn't decrease the size of the materialized table at all.
If I remove the above subquery, in its entirety, and change line 35 from:
FROM (SELECT `catalogprices`.`price`,to:
FROM (SELECT `commerce_products`.`defaultPrice` AS `price`,then the execution time of the query decreases to 65ms. In short, it runs about 30 times faster. We don't use catalog pricing, so it is an effective tradeoff to remove that subquery. Unfortunately, there is no way to do it without patching the Craft Commerce source code.
If you want to test the modified query, here it is:
SELECT `elements`.`id`,
`elements`.`canonicalId`,
`elements`.`fieldLayoutId`,
`elements`.`uid`,
`elements`.`enabled`,
`elements`.`archived`,
`elements`.`dateLastMerged`,
`elements`.`dateCreated`,
`elements`.`dateUpdated`,
`elements_sites`.`id` AS `siteSettingsId`,
`elements_sites`.`siteId`,
`elements_sites`.`title`,
`elements_sites`.`slug`,
`elements_sites`.`uri`,
`elements_sites`.`content`,
`elements_sites`.`enabled` AS `enabledForSite`,
`commerce_products`.`id`,
`commerce_products`.`typeId`,
`commerce_products`.`postDate`,
`commerce_products`.`expiryDate`,
`subquery`.`price` AS `defaultPrice`,
`purchasablesstores`.`basePrice` AS `defaultBasePrice`,
`commerce_products`.`defaultVariantId`,
`purchasables`.`sku` AS `defaultSku`,
`purchasables`.`weight` AS `defaultWeight`,
`purchasables`.`length` AS `defaultLength`,
`purchasables`.`width` AS `defaultWidth`,
`purchasables`.`height` AS `defaultHeight`,
`sitestores`.`storeId`,
`structureelements`.`root`,
`structureelements`.`lft`,
`structureelements`.`rgt`,
`structureelements`.`level`,
`structureelements`.`structureId`
FROM (SELECT `commerce_products`.`defaultPrice` AS `price`,
`elements`.`id` AS `elementsId`,
`elements_sites`.`id` AS `siteSettingsId`,
`structureelements`.`structureId`
FROM `craft_elements` `elements`
INNER JOIN `craft_commerce_products` `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
LEFT JOIN `craft_structureelements` `structureelements`
ON (`structureelements`.`elementId` = `elements`.`id`) AND (EXISTS (SELECT *
FROM `craft_structures` use index (primary)
WHERE (`id` = `structureelements`.`structureId`)
AND (`dateDeleted` IS NULL)))
LEFT JOIN `craft_commerce_site_stores` `sitestores` ON `elements_sites`.`siteId` = `sitestores`.`siteId`
WHERE (`commerce_products`.`defaultSku` = 'LATLFE5U-25F-6BG256C')
AND (`elements_sites`.`siteId` = 5)
AND (((`elements`.`enabled` = 1) AND (`elements_sites`.`enabled` = 1)) AND
(`commerce_products`.`postDate` <= '2025-05-24 15:06:01') AND
((`commerce_products`.`expiryDate` IS NULL) OR (`commerce_products`.`expiryDate` > '2025-05-24 15:06:01')))
AND (`elements`.`archived` = 0)
AND (`elements`.`dateDeleted` IS NULL)
AND (`elements`.`draftId` IS NULL)
AND (`elements`.`revisionId` IS NULL)
ORDER BY `structureelements`.`lft`, `commerce_products`.`postDate` DESC, `elements`.`id` DESC
LIMIT 1) `subquery`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`siteSettingsId`
INNER JOIN `craft_commerce_products` `commerce_products` ON `commerce_products`.`id` = `subquery`.`elementsId`
LEFT JOIN `craft_commerce_site_stores` `sitestores` ON `elements_sites`.`siteId` = `sitestores`.`siteId`
LEFT JOIN `craft_commerce_purchasables` `purchasables`
ON `purchasables`.`id` = `commerce_products`.`defaultVariantId`
LEFT JOIN `craft_commerce_purchasables_stores` `purchasablesstores`
ON `purchasablesstores`.`purchasableId` = `commerce_products`.`defaultVariantId` and
`sitestores`.`storeId` = `purchasablesstores`.`storeId`
LEFT JOIN `craft_structureelements` `structureelements`
ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND
(`structureelements`.`structureId` = `subquery`.`structureId`)
ORDER BY `structureelements`.`lft`, `commerce_products`.`postDate` DESC, `elements`.`id` DESC;Craft CMS version
5.7.0
Craft Commerce version
5.3.4
PHP version
8.4
Operating system and version
64bit Amazon Linux 2023
Database type and version
MySQL 8.4
Image driver and version
Not applicable
Installed plugins and versions
Not applicable