I wanted to generate random products for certain categories on my home page. Came across this code:

// $_productCollection = $this->getLoadedProductCollection();
 $_helper = $this->helper('catalog/output');
 $_category = Mage::getModel('catalog/category')->load($this->getCategoryId());
 $_productCollection = Mage::getResourceModel('reports/product_collection')
                   ->addAttributeToFilter('small_image', array('neq' => 'no_selection'))
 $_productCollection->getSelect()->order(new Zend_Db_Expr('RAND()'));                  
 $_productCollection->setPage(1, 5);

Now when I added it to my modified list.phtml it wouldn't load. Playing around the only way I could get the above to work was to put the following BEFORE the above code.

               ->addAttributeToFilter('small_image', array('neq' => 'no_selection'))
$_helper = $this->helper('catalog/output');

This is working, however it is resulting in slow loading times on the page where this code is being called. I tried modifying the first code block to include elements of the 2nd block but no dice.

What can be changed to make this code run more efficiently?

1 Answer 1


ORDER BY RAND() is unperformant 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 = 5;
$candidateIds = $_productCollection->getAllIds();
    $choosenIds = [];
    $maxKey = count($candidateIds)-1;
    while (count($choosenIds) < $numberOfItems)) {
        $randomKey = mt_rand(0, $maxKey);
        $choosenIds[$randomKey] = $candidateIds[$randomKey];

Then, specify only the attributes that you need to load instead of * and join price and URL indexes if you want to show the right price and product URL:

  • I was experiencing some very high CPU usage and serious lags when people would access pages I was using that code. It's been a while since I've removed it but maybe I will revisit it now. Thank you
    – SR_Magento
    Commented Aug 14, 2015 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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