Magento – How to optimize custom product collection

custom-collectionmagento-1.7product-collection

Actually i want to optimize my store. It takes 5 to 6s. I m using ready theme from theme forest. According to theme m doing some of customization according my requirements which i needed. ON this theme Already displayed new products collection and also sales collection. Now i want to load 2 more collection according to attributes.

So issue is on homepage product collection is load 4 times. If collection is load two times on homepage store will be load within 2s for now. For now i optimize some how my store but still will takes times to load within 4.5 to 5s because of product collection.

I am trying to get product collection by attribute. below code is working perfectly. It takes too much time to load. That's why my store is working too much slow. If i removed this code my store is load within 2 to 2.5s. 

$_productCollection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToFilter('popular_walldecal', array('eq' => 1))
    ->setPageSize(8);

$_collectionSize = $_productCollection->count();

foreach ($_productCollection as $product):
    $_product = Mage::getModel("catalog/product")->load($product->getID());
endforeach;

I have tried above code. If product collection loads within in ms. It will be affect on my store to page loading issue.

In above code i need to load again 

$_product = Mage::getModel("catalog/product")->load($product->getID());

Otherwise i can't get all product data.

Can you help me to resolve out this issue? How to optimize this code.
Thanks in advance.

Best Answer

So let's go trough this line by line

$_productCollection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToFilter('popular_walldecal', array('eq' => 1))
    ->setPageSize(8);

Perfect, no need to change this. Just make sure you have the flat product data enabled under System > Configuration > Use Flat Catalog Product and the attribute popular_walldecal settings Use in product listing set to yes

$_collectionSize = $_productCollection->count();

Loads the whole collection, I suggest using getSize() instead of count since the first doesn't load the collection.

foreach ($_productCollection as $product):
    $_product = Mage::getModel("catalog/product")->load($product->getID());
endforeach;

Now here's the real issue. The collection just executes one query over one table (catalog_flat_product_[id]) but loading the product model in the loop executes a whole bunch of queries per iteration. It's better to add the required data to the collection.

$_productCollection = Mage::getResourceModel('catalog/product_collection')
   ->addAttributeToFilter('popular_walldecal', array('eq' => 1))
   ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
   ->setPageSize(8);

$_productCollection->addFinalPrice()
   ->addUrlRewrite();

Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection(_productCollection);
Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($_productCollection);

This will

  • filter by popular_walldecal
  • add all the attributes in the catalog_flat_product_[id] table
  • set the page size to 8
  • add the final price and url rewrite data for the product url
  • filter the collection by visibility in catalog
  • filter the collection by status enabled

And now you're good to go!

Related Topic