Magento - Configurable products with 1000's of color options slow product detail page load time

Question

We have a magento store with approx 5k configurable products. And for those products, we have 29k+ options for the "color" attribute. This is severely slowing down our store (10-20 seconds to load product detail pages).

We have been told by many developers that they could use direct queries to get around the speed issue. However, not one of them has actually been able to accomplish this task.

Has anyone here done this before successfully?? Any suggestions, code, etc.. would be much appreciated. I have spent a lot of time in here looking around and have not seen any specific answers to this problem.

Could you get away with pre-generating the HTML for the colour options into a static HTML file, and just include that instead? — andrewsi
Will the color picker work for you or do you need only specific colors? — user487772
29k options for color attribute :O how do do even name those colors — sulabh
I'd start by profiling the page and find out where the slow queries are. Chances are it's not the core Magento code that's the problem, but rather a runaway query OR Magento trying to load up a product option select with too many colors. Even if it's not, knowing what's slowing down your page is the first step to speeding it up. — Alan Storm

LogIN LogIN · Accepted Answer · 2017-01-03T12:30:03

Since I had similar or maybe the exactly the same issue today I wanted to post solution:

In my case I had configurable attribute with maybe 20k options. Product Detail Page took forever to load.

After some research I found other ppl also had similar issue: Link 1 Link 2

Solution was the following:

I copied: /app/code/core/Mage/ConfigurableSwatches/Model/Resource/Catalog/Product/Attribute/Super/Collection.php

to local: /app/code/local/Mage/ConfigurableSwatches/Model/Resource/Catalog/Product/Attribute/Super/Collection.php

(Please note you should change: $fallbackStoreId variable)

and made following change to speed up the things:

/**
 * Load attribute option labels for current store and default (fallback)
 *
 * @return $this
 */
protected function _loadOptionLabels()
{
    if ($this->count()) {
        $labels = $this->_getOptionLabels();
        foreach ($this->getItems() as $item) {
            $item->setOptionLabels($labels);
        }
    }
    return $this;
}

/**
 * Get Option Labels
 *
 * @return array
 */
protected function _getOptionLabels()
{
    $attributeIds = $this->_getAttributeIds();
    // Mage_Catalog_Model_Abstract::DEFAULT_STORE_ID
    $fallbackStoreId = 2;

    $select = $this->getConnection()->select();
    $select->from(array('options' => $this->getTable('eav/attribute_option')))
        ->join(
            array('labels' => $this->getTable('eav/attribute_option_value')),
            'labels.option_id = options.option_id',
            array(
                'label' => 'labels.value',
                'store_id' => 'labels.store_id',
            )
        )
        ->where('options.attribute_id IN (?)', $attributeIds)
        ->where(
            'labels.store_id IN (?)',
            array($fallbackStoreId, $this->getStoreId())
        );


    $labels = array();
    $thisClass = $this;
    Mage::getSingleton('core/resource_iterator')->walk(
        $select,
        array(function($args) use (&$thisClass){
            $data = $args['row'];
            $labels[$data['option_id']][$data['store_id']] = $data['label'];

        })
    );

    return $labels;
}

/**
 * Get Attribute IDs
 *
 * @return array
 */
protected function _getAttributeIds()
{
    $attributeIds = array();
    foreach ($this->getItems() as $item) {
        $attributeIds[] = $item->getAttributeId();
    }
    $attributeIds = array_unique($attributeIds);

    return $attributeIds;
}

Magento - Configurable products with 1000's of color options slow product detail page load time

1 Answers