search

craftcms / commerce

Fully integrated ecommerce for Craft CMS.
https://craftcms.com/commerce
Other
218 stars 170 forks source link

Slow Variant Queries #2674

Closed davist11 closed 1 year ago

davist11 commented 2 years ago

Description

We have a Commerce site with a lot of products and variants (~600k variants). As the number of products/variants increased, we are seeing extremely slow performance in a number of places.

Example 1: Loading the products section in the CP

Visiting admin/commerce/products/{productType} is incredibly slow. I loaded a specific product type that has 36 products and 720 variants. It takes ~20 seconds, and the debug bar doesn't even show on these requests (the debug bar XHR request responds with a 404), so it's hard to dig into specific queries. Even a product type with 4 products and 16 variants takes ~20 seconds on initial load.

It's really only the initial load of a product type page, once you start navigating to other product types (since it's using XHR requests), this appears to be relatively quick.

Example 2: Variant query in PHP

We are utilizing SEOmatic and adding additional sitemaps to account for URLs to all variants. We figured paginating 1000 variants per page wouldn't be too bad. So we have a variant query in PHP:

$variants = Variant::find()
    ->offset(($page - 1) * $limit)
    ->limit($limit)
    ->all();

Where $limit = 1000 and $page is being pulled from a query param /sitemap-variants.xml?page=1. Our devops team pulled some logs for slow queries, and it seems like this one is impactful:

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, 
`elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, 
`elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS 
`enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`, `commerce_variants`.`id`, 
`commerce_variants`.`productId`, `commerce_variants`.`isDefault`, `commerce_variants`.`sku`, 
`commerce_variants`.`price`, `commerce_variants`.`sortOrder`, `commerce_variants`.`width`, 
`commerce_variants`.`height`, `commerce_variants`.`length`, `commerce_variants`.`weight`, 
`commerce_variants`.`stock`, `commerce_variants`.`hasUnlimitedStock`, `commerce_variants`.`minQty`, 
`commerce_variants`.`maxQty`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_address1`, 
`content`.`field_address2`, `content`.`field_altParts`, `content`.`field_alt`, `content`.`field_announcementHeading`, 
`content`.`field_availability`, `content`.`field_availabilityDescription`, `content`.`field_avataxCustomerUsageType`, 
`content`.`field_avataxTaxCode`, `content`.`field_backgroundColor`, `content`.`field_bodyHookBottom`, 
`content`.`field_bodyHookTop`, `content`.`field_ctaButton`, `content`.`field_newsButton`, `content`.`field_cadKey`, 
`content`.`field_city`, `content`.`field_code`, `content`.`field_company`, `content`.`field_contactHeading`, 
`content`.`field_deliveryInstructions`, `content`.`field_announcementDescription`, `content`.`field_description`, 
`content`.`field_newsDescription`, `content`.`field_descriptionWithHeadings`, `content`.`field_displayFamily`, 
`content`.`field_displayMetricValuesByDefault`, `content`.`field_email`, `content`.`field_emailAddresses`, 
`content`.`field_embed`, `content`.`field_familyDescription`, `content`.`field_familyHeading`, `content`.`field_fax`, 
`content`.`field_featuredProductsDescription`, `content`.`field_finishColor`, 
`content`.`field_flattenVariants_ykmzgarz`, `content`.`field_formerTitle`, `content`.`field_groupByFirstAttribute`, 
`content`.`field_h1`, `content`.`field_headHook`, `content`.`field_complianceHeading`, `content`.`field_ctaHeading`, 
`content`.`field_heading`, `content`.`field_popularProductsHeading`, `content`.`field_hideCads`, 
`content`.`field_hideNavigation`, `content`.`field_hubspotFormId`, `content`.`field_icon`, 
`content`.`field_inPageNavOrientation`, `content`.`field_includeNoneAsTheFirstOption`, 
`content`.`field_industriesServedDescription`, `content`.`field_industryYears`, `content`.`field_industriesIntro`, 
`content`.`field_intro`, `content`.`field_thoughtLeadershipIntro`, `content`.`field_label`, `content`.`field_basicLink`, 
`content`.`field_linkList`, `content`.`field_location`, `content`.`field_locationDescription`, 
`content`.`field_locationHeading`, `content`.`field_maxValue`, `content`.`field_minValue`, `content`.`field_newsType`, 
`content`.`field_numberOfHoles`, `content`.`field_numberOfWaves`, `content`.`field_overlayHeading`, 
`content`.`field_overlayStyle`, `content`.`field_ctaOverline`, `content`.`field_overrideDescription`, 
`content`.`field_overrideTitle`, `content`.`field_password`, `content`.`field_paymentMethod`, `content`.`field_phone`, 
`content`.`field_phoneNumbers`, `content`.`field_pimData`, `content`.`field_poNumber`, `content`.`field_pricingTier`, 
`content`.`field_primaryButton`, `content`.`field_primaryDescription`, `content`.`field_primaryOverline`, 
`content`.`field_productType`, `content`.`field_isPurchasable`, `content`.`field_redirectUrl`, 
`content`.`field_resourceIcon`, `content`.`field_secondaryBackgroundColor`, `content`.`field_secondaryDescription`, 
`content`.`field_secondaryIcon`, `content`.`field_secondaryOverline`, `content`.`field_selectedShippingType`, 
`content`.`field_seo`, `content`.`field_series`, `content`.`field_shippingAccountMethod`, 
`content`.`field_shippingAccountNumber`, `content`.`field_shippingAccountService`, 
`content`.`field_shortDescription`, `content`.`field_shortTitle`, `content`.`field_socialLinks`, `content`.`field_source`, 
`content`.`field_state`, `content`.`field_heroTagline`, `content`.`field_termsCustomer`, `content`.`field_ctaText`, 
`content`.`field_threadClass2A`, `content`.`field_title1`, `content`.`field_title2`, `content`.`field_totalCoils`, 
`content`.`field_basicUrl`, `content`.`field_useAlternateLayout`, `content`.`field_useReplicaIndex`, 
`content`.`field_videoURL_licwqxle`, `content`.`field_visualId`, `content`.`field_zip`
FROM (
    SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
    FROM `craft_elements` `elements`
    INNER JOIN `craft_commerce_variants` `commerce_variants` ON `commerce_variants`.`id` = `elements`.`id`
    LEFT JOIN `craft_commerce_products` `commerce_products` ON `commerce_variants`.`productId` = `commerce_products`.`id`
    LEFT JOIN `craft_commerce_producttypes` `commerce_producttypes` ON `commerce_products`.`typeId` = `commerce_producttypes`.`id`
    INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
    INNER JOIN `craft_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
    WHERE (`elements_sites`.`siteId`=2) AND (`elements`.`archived`=0) AND ((`elements`.`enabled`=1) AND (`elements_sites`.`enabled`=1)) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
    ORDER BY `commerce_variants`.`sortOrder` LIMIT 1000 OFFSET 0
) `subquery`
INNER JOIN `craft_commerce_variants` `commerce_variants` ON `commerce_variants`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
ORDER BY `commerce_variants`.`sortOrder`

Which takes 4 seconds. Digging in a little further, it seems like its the subquery is what is the slow part:

SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_commerce_variants` `commerce_variants` ON `commerce_variants`.`id` = `elements`.`id`
LEFT JOIN `craft_commerce_products` `commerce_products` ON `commerce_variants`.`productId` = `commerce_products`.`id`
LEFT JOIN `craft_commerce_producttypes` `commerce_producttypes` ON `commerce_products`.`typeId` = `commerce_producttypes`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
WHERE (`elements_sites`.`siteId`=2) AND (`elements`.`archived`=0) AND ((`elements`.`enabled`=1) AND (`elements_sites`.`enabled`=1)) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `commerce_variants`.`sortOrder` LIMIT 1000 OFFSET 0

Even reducing the limit to something small, and this query still takes the same time to execute, which makes me think it's something to do with the overall number of records. Is there some way to optimize this query that I'm missing?

Steps to reproduce

  1. Have a commerce install with a large number of products/variants (we are happy to send our DB)
  2. Execute a variant query (or even just the subquery above)

Additional info

Amazon S3 1.3.0
Asset Rev 6.0.2
Avatax 2.1.7
Classnames 1.0.3
Command Palette 3.1.4
Craft Commerce 3.4.7
Feed Me 4.4.0
Imgix 2.1.0
Linkit 1.1.12.1
Navigation 1.4.22
Postie 2.4.11
Ray 1.1.5
reCAPTCHA 1.5.2
Redactor 2.8.8
Retour 3.1.65
Scout dev-master
SEOmatic 3.4.17
Stripe for Craft Commerce 2.4.0
Style Inliner 2.3.0
Super Table 2.7.0
Table Maker 2.0.1

Please let us know if there is any additional info we can provide that would be helpful. Thanks so much!

lukeholder commented 1 year ago

Please send your DB into support@craftcms.com and we can try to reproduce the performance issue and take a look. Thanks. Likely some indexes are mission on some tables, and adding them (back?) may fix it. Thanks.