2

Currently used code for retrieving filtered product collection:

    $products = Mage::getModel('catalog/product')
        ->setStoreId($storeId)
        ->getCollection()
        ->addAttributeToSort()
        ->addStoreFilter()
        ->addAttributeToFilter('category_id', array('in' => $rootCategoryId))
        ->addAttributeToFilter('type_id', 'configurable')
        ->addAttributeToFilter('sku', array('neq' => $attribute_set_id2))
        ->addAttributeToFilter('visibility', array('in' => array(4) ))
        ->addCategoryFilter(Mage::getModel('catalog/category')->load($categoryID));

   $products->getSelect()->order(new Zend_Db_Expr('RAND()'));

Which I think is fine. Issue begins when I use following code for displaying products from previous collection as:

    foreach($products as $product):
        if ($counter == 6) { return $productArray; }
        $fullProduct = Mage::getModel('catalog/product')->load($product->getId());
        $images_obj = $this->helper('catalog/image')->init($fullProduct, 'small_image')->resize(252, 308);
        $images[] = (string)$images_obj;
        $productArray[$product->getId()]['image'] = (string)$images_obj;
        $productArray[$product->getId()]['url'] = $fullProduct->getProductUrl();
        $productArray[$product->getId()]['name'] = $fullProduct->getName();
        $productArray[$product->getId()]['price'] = Mage::helper('core')->currency($fullProduct->getFinalPrice(),true);
        $productArray[$product->getId()]['category'] = $fullProduct->getCategory();
        $counter++;
    endforeach;

Real performance killer here is:

$fullProduct = Mage::getModel('catalog/product')->load($product->getId());

Wondering is it possible to somehow store product IDs from collection and use later in foreach loop? Or any different workaround on this issue so script does not hit DB so harsh. Many thanks

  • Did my answer help you? Then it would be nice if you take your time to accept it :) – Fabian Schmengler Jul 25 '16 at 21:46
4

Wondering is it possible to somehow store product IDs from collection and use later in foreach loop?

It's possible to load only the ids from the collection (with getAllIds()) and sometimes that's a viable solution, but in your case I don't see how that helps you.

Instead you should prepare your collection such that a "full product" load is not necessary anymore (This is not a workaround, but the right way™). Add this code before the foreach to include all data that you usually need for product lists in the frontend:

$products
        ->addMinimalPrice()
        ->addFinalPrice()
        ->addTaxPercents()
        ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
        ->addUrlRewrite();

This prepares the product collection to load the necessary data to display prices, the product link and any attributes configured as "used in product listing", but not more. You might want to specify only the attributes that you really need in addAttributeToSelect instead for further improvement:

->addAttributeToSelect(['image', 'name', /*...*/])

Other Performance Issues

Improve Random Order

ORDER BY RAND() is unperformant on large tables because it results in a resource intensive temp table copy. It has to load all results into a temporary table, assign a random number to each row and then sort without any index. Instead we retrieve all candidate ids (this is faster and the amount of data is managable even for large catalogs), pick some randomly and retrieve these rows directly. You can read about it in detail in my blog: http://www.schmengler-se.de/en/2015/09/show-random-products-in-magento-you-are-doing-it-wrong/

To do so, insert this code after the filters for the collection have been applied:

$numberOfItems = 6;
$candidateIds = $products->getAllIds();
$choosenIds = [];
$maxKey = count($candidateIds)-1;
while (count($choosenIds) < $numberOfItems)) {
    $randomKey = mt_rand(0, $maxKey);
    $choosenIds[$randomKey] = $candidateIds[$randomKey];
}    
$products->addIdFilter($choosenIds);

That's an example, where getAllIds() is useful.

Limit Page Size

As soon as your code hits the foreach, all products are loaded into memory, you just stop processing the result after the 6th. Add a LIMIT to the query with this code:

$products->setPage(1, 6);

Note that this is not really necessary anymore if you applied the previous improvement with addIdFilter() because there we already limit the result by filtering 6 explicit ids.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.