Retrieve product status query optimization in core magento

Question

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 FROM cataloginventory_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:

1. We have productIds array in IN clause which can vary based on product collection query at different pages or blocks.

2. getProductStatus method is a common method which retrieve product status and return array as key product id, value - stock status.

3. 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.

4. 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.

Answer 1

This query is also sometimes flagged up as a slow running one in my New Relic monitoring. The query analysis suggests:

"You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key."

The primary index has the following fields: - product_id - website_id - stock_id

If you created an additional index which also had the 'stock_status' field in there, then it would be able to execute the query entirely using the index, without having to pull back any data records.

(In truth, I can't see why the query is so slow anyway: my running a similar query in msql workbench takes approx. 0.031 secs. on a table with 3,821 records)

I haven't tried the additional index myself, but it should be quite an unobtrusive solution.