One of our client is having more than 1 Lack of products and below retrieve product status query is taking around 9-11 sec on production server.
SELECT
cataloginventory_stock_status
.product_id
,cataloginventory_stock_status
.stock_status
FROMcataloginventory_stock_status
WHERE (product_id IN('78724', '78723', '78722'.....upto 1 Lack productids here)) AND (stock_id=1) AND (website_id=1);
After investigation I have find that this query run after loading product collection in Magento. Query getting executed by an observer method in Mage_CatalogInventory_Model_Observer that is triggered for product collection after load event.
Config file path: app/code/core/Mage/CatalogInventory/etc/config.xml
Event: catalog_product_collection_load_after
class: cataloginventory/observer
method: addStockStatusToCollection
Observer file path: `app/code/core/Mage/CatalogInventory/Model/Observer.php
function: addStockStatusToCollection($observer)
addStockStatusToCollection
method further calls addStockStatusToProducts($productCollection)
method in app/code/core/Mage/CatalogInventory/Model/Stock/Status.php
function: addStockStatusToProducts($productCollection)
addStockStatusToProducts method
calls getProductStatus($productIds, $websiteId, $stockId)
in app/code/core/Mage/CatalogInventory/Model/Resource/Stock/Status.php
Final SQL Select query is getting generated from function getProductStatus($productIds, $websiteId, $stockId = 1)
app/code/core/Mage/CatalogInventory/Model/Resource/Stock/Status.php
public function getProductStatus($productIds, $websiteId, $stockId = 1)
{
if (!is_array($productIds)) {
$productIds = array($productIds);
}
$select = $this->_getReadAdapter()->select()
->from($this->getMainTable(), array('product_id', 'stock_status'))
->where('product_id IN(?)', $productIds)
->where('stock_id=?', (int)$stockId)
->where('website_id=?', (int)$websiteId);
return $this->_getReadAdapter()->fetchPairs($select);
}
I have also checked SQL query optimization for large IN clauses and find that it can be optimize by replacing IN clause with JOIN.
However, I am not sure if we would be able to replace IN clause by JOIN in getProductStatus method because of following reasons:
We have productIds array in IN clause which can vary based on product collection query at different pages or blocks.
getProductStatus method is a common method which retrieve product status and return array as key product id, value - stock status.
We don't have table2(for JOIN) which stores only productIds which are coming as a parameter, as productIds may vary based on product collection query.
We can't use catalog_product_entity table for join because catalog_product_entity table has product id's for all the products in system and we need only the productIds coming in getProductStatus method parameter.
Please suggest how can we optimize above IN clause.