Variants taking an extremely long time to generate

  • I have a set of products with options:

    10 Colours

    2 Style Options

    4 Handle Options

    When generating the variants it takes approximately 16 (sixteen) minutes on a local development server. During this time the server is inaccessible.

    Obviously we cannot run with this. Has anyone experienced this and has anyone a suggestion as to what we might be able to do to remedy this?

  • There are lines in the debug that show where the time is being spent:

    738.1921 14.9622 SELECT * FROM perch3_shop_products WHERE sku='FDCD-PCD-CW-TG23-KB' AND productDeleted IS NULL LIMIT 1 - 14 seconds?

    773.335 35.0981 SELECT * FROM perch3_shop_products WHERE sku='FDCD-PCD-CW-TG154-LV' AND productDeleted IS NULL LIMIT 1 - 35 seconds?

    919.8407 51.2922 SELECT * FROM perch3_shop_products WHERE sku='FDCD-PCD-A-TG23-KA' AND productDeleted IS NULL LIMIT 1 - 51 seconds?

    Any help here would be very very welcome.

  • Are you indexing all the fields in the product template? You can use the no-index attribute on the fields you don't have to index. I'm not sure if this will solve your problem but you could always try.

  • I haven't assigned no index to any fields, the products template does have a lot of fields.

    Does the no-index make the field invisible to search? Or what effect does adding that have?

    There are only a few fields really need to be visible to search.

  • Quote

    "You normally only want to index fields that you need to filter and/or sort by. The more fields you index, the more likely your queries going to be slower (though this depends on other factors too)."

    As taken from this article from Hussein. I believe it should also work for products.

  • Sorry for the late reply. Some of our products are multi-dimensional with various options, they are doors:

    10 Colours
    4 Handle Styles

    6 Glass Styles

    2 Knock options

    Just adding one or two products with these options is driving the rows in the shop_index into the 10's of thousands, even using the base product template.

    This is causing time-out issues when we are trying to republish products through a third party app.