I’m looking for a faster way to retrieve a list of SKUs of "in stock" products.

I’m using this code

$products_collection = \Mage::getResourceModel('catalog/product_collection')->addAttributeToSelect('sku');


return $products_collection->getColumnValues('sku');

but it’s quite slow, because first it gets all products then filter them by "in stock".

Are there any faster query to execute? Maybe using an inverse style: first get only IDs of "in stock" products then get only their SKUs?

2 Answers 2


Try this

$resource = Mage::getResourceSingleton('catalog/product');
$read = $resource->getReadConnection();
$select = $read->select()
    ->from(array('p' => $resource->getTable('catalog/product')), 'sku')
        array('i' => $resource->getTable('cataloginventory/stock_item')),
        'i.product_id = p.entity_id AND i.is_in_stock=1',

$skus = $read->fetchCol($select);
  • It looks like that the Magento addInStockFilterToCollection() has a more complete ON clause: i.product_id = e.entity_id AND ((i.use_config_manage_stock = 0 AND i.manage_stock = 1 AND i.is_in_stock = 1) OR (i.use_config_manage_stock = 0 AND i.manage_stock = 0) OR (i.use_config_manage_stock = 1 AND i.is_in_stock = 1)), but this one looks enough in my specific case.
    – Riccardo
    Commented Jul 6, 2017 at 13:44

You can try join() and where()

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->getSelect()->join('cataloginventory_stock_status', 'cataloginventory_stock_status.product_id = e.entity_id', array('stock_status'));
$collection->getSelect()->where("`cataloginventory_stock_status`.`stock_status` = 1");

$collection is loaded with only In Stock items. Hope this helps.

  • I just tested it: unfortunately it looks slow as mine.
    – Riccardo
    Commented Jul 6, 2017 at 9:25

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.