Support » Plugin: WooCommerce » Product page is very slow when using lots of variations

  • Resolved daniesy

    (@daniesy)


    I have products on my website with 3000+ variations. This makes everything very slow, from loading the initial variation to calculating the price whenever i change a product attribute. Is there something i can do to speed things up? I’m not using any special third party plugins that might slow down the shop…
    Thanks in advance!

Viewing 8 replies - 1 through 8 (of 8 total)
  • Plugin Support laceyrod

    (@laceyrod)

    Automattic Happiness Engineer

    Hi there!

    Thanks for reaching out! That is, indeed, a lot of variations!

    Here are some general tips for troubleshooting a slow site, if you’d like to take a look: https://docs.woocommerce.com/document/troubleshooting-a-slow-site/

    Utilizing a caching plugin might help, but it can also come with its own complications.

    Another option might be to increase your max_input_vars, as this can typically affect being able to save/load a lot of product data: https://docs.woocommerce.com/document/problems-with-large-amounts-of-data-not-saving-variations-rates-etc/#php-539-and-max_input_vars

    Hope this helps!

    Hey @laceyrod, thanks for your reply.
    I think I know the reason for why the site is loading slowly and it’s due to the fact the products I’ve added have a lot of variations. So, from my point of view, there’s no troubleshooting needed. I’ve watched the queries that are done when getting the variation or the price and they’re huge:

    SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'attribute_pa_type-impression') LEFT JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )  LEFT JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id AND mt3.meta_key = 'attribute_pa_faconnage' )  LEFT JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id )  LEFT JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id AND mt5.meta_key = 'attribute_pa_support' )  LEFT JOIN wp_postmeta AS mt6 ON ( wp_posts.ID = mt6.post_id )  LEFT JOIN wp_postmeta AS mt7 ON (wp_posts.ID = mt7.post_id AND mt7.meta_key = 'attribute_pa_envoi-bat' )  LEFT JOIN wp_postmeta AS mt8 ON ( wp_posts.ID = mt8.post_id )  LEFT JOIN wp_postmeta AS mt9 ON (wp_posts.ID = mt9.post_id AND mt9.meta_key = 'attribute_pa_signature-impression-cool' )  LEFT JOIN wp_postmeta AS mt10 ON ( wp_posts.ID = mt10.post_id )  LEFT JOIN wp_postmeta AS mt11 ON (wp_posts.ID = mt11.post_id AND mt11.meta_key = 'attribute_pa_point-livraison' )  LEFT JOIN wp_postmeta AS mt12 ON ( wp_posts.ID = mt12.post_id )  LEFT JOIN wp_postmeta AS mt13 ON (wp_posts.ID = mt13.post_id AND mt13.meta_key = 'attribute_pa_choisissez-delai' )  LEFT JOIN wp_postmeta AS mt14 ON ( wp_posts.ID = mt14.post_id )  LEFT JOIN wp_postmeta AS mt15 ON (wp_posts.ID = mt15.post_id AND mt15.meta_key = 'attribute_pa_envoi-pour-revendeur' ) WHERE 1=1  AND wp_posts.post_parent = 162820  AND ( 
      ( 
        ( wp_postmeta.meta_key = 'attribute_pa_type-impression' AND wp_postmeta.meta_value IN ('','recto-seule') ) 
        OR 
        mt1.post_id IS NULL
      ) 
      AND 
      ( 
        ( mt2.meta_key = 'attribute_pa_faconnage' AND mt2.meta_value IN ('','coupe-au-format-coins-carres') ) 
        OR 
        mt3.post_id IS NULL
      ) 
      AND 
      ( 
        ( mt4.meta_key = 'attribute_pa_support' AND mt4.meta_value IN ('','dibond-reynolite-2-mm-blanc-2900-g-m%c2%b2') ) 
        OR 
        mt5.post_id IS NULL
      ) 
      AND 
      ( 
        ( mt6.meta_key = 'attribute_pa_envoi-bat' AND mt6.meta_value IN ('','oui-10-e') ) 
        OR 
        mt7.post_id IS NULL
      ) 
      AND 
      ( 
        ( mt8.meta_key = 'attribute_pa_signature-impression-cool' AND mt8.meta_value IN ('','non') ) 
        OR 
        mt9.post_id IS NULL
      ) 
      AND 
      ( 
        ( mt10.meta_key = 'attribute_pa_point-livraison' AND mt10.meta_value IN ('','1-point') ) 
        OR 
        mt11.post_id IS NULL
      ) 
      AND 
      ( 
        ( mt12.meta_key = 'attribute_pa_choisissez-delai' AND mt12.meta_value IN ('','express-j2') ) 
        OR 
        mt13.post_id IS NULL
      ) 
      AND 
      ( 
        ( mt14.meta_key = 'attribute_pa_envoi-pour-revendeur' AND mt14.meta_value IN ('','envoi-en-marque-blanche') ) 
        OR 
        mt15.post_id IS NULL
      )
    ) AND wp_posts.post_type = 'product_variation' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order ASC LIMIT 0, 1

    Are there any steps I can take on the database that would improve query times? Is there a maximum amount of product/variations Woocommerce supports? I’ve noticed that once the wp_posts and wp_postmeta tables get big, the products take a lot of time to load.

    Also changing max_input_vars does not help me because I’m not trying to post anything. I’m experiencing issues on product pages, especially on those with lots of variations. This is a database issue.

    Plugin Support Hari Shanker R

    (@harishanker)

    Automattic Happiness Engineer

    Hi @daniesy

    Are there any steps I can take on the database that would improve query times? Is there a maximum amount of product/variations Woocommerce supports? I’ve noticed that once the wp_posts and wp_postmeta tables get big, the products take a lot of time to load.

    There is no maximum number of products/variations supported by WooCommerce. These numbers are limited only by the capability of your web server.

    We are trying to optimize WooCommerce in upcoming versions by adding more CRUD (Create Read Update Delete) methods within the database for WooCommerce. We have made a lot of such changes in WooCommerce 3.0, and are continuing to do so in upcoming releases (like in WooCommerce 3.6).

    For general database optimization until then, you might want to follow some of the tips listed here: https://www.cloudways.com/blog/wordpress-database-optimization-guide/

    In the long run, as your business starts growing, I suggest that you follow these steps:

    * Start slowly, and load testing a few thousand products at a time. This would help to show how the system performs as the store grows.
    * Plan your server upgrades for the future now. Make sure your database is set in a primary/clone/clone structure, and as you grow plan to upgrade to multiple databases with the same structure, as well as multiple web front ends for serving the store. Primary/clone/clone is a database architecture where there is a master database which handles all the writing of information to the database, and two slave databases (clones of the master) which are used for reading information out. This balances the load when a website is receiving lots of traffic.
    * Use good front end caching tool. [Batcache](https://wordpress.org/plugins/batcache/) is a great plugin that will do the job.
    * Be mindful of how much product information is shown on the homepage, and each screen. Having multiple sections on the homepage with, for example, feature, popular, and latest books, would be 3 queries. The lower the amount of queries, the faster your site will run

    We hope these tips will help you while we are in the process of restructuring WooCommerce and preparing it to scale more efficiently.

    Plugin Support Hari Shanker R

    (@harishanker)

    Automattic Happiness Engineer

    Hi @daniesy

    I just wanted to follow-up with you on this thread; hope you were able to fix this problems with slow page load issues, that we had discussed here.

    I’m marking this thread as Resolved as we have not heard back from you in a while. If the problem persists though, feel free to reopen this, we would be happy to look into this for you.

    All the best!

    I wanted to add that I have the same problem. Even worse: I can’t even update the product page with thousands of variations…

    @daniesy
    https://www.wpintense.com/product/scalability-pro/

    This whole plugin is awesome, but scroll down a bit & specifically read what he’s done with the left_join options in his plugin. It really is pretty remarkable.

    sniperkill

    (@sniperkill)

    I am planning to add 100 variations to a product and thought it is too much, but after reading the above comments, I think a product page with 100 variations would load perfectly fine. Will it?

    • This reply was modified 3 months ago by sniperkill.
Viewing 8 replies - 1 through 8 (of 8 total)
  • You must be logged in to reply to this topic.