Magento – Mass delete disabled products with SQL

databasemagento-1.9productsproducts-management

We have about 68 000 disabled products in our database. We need somehow to delete all disabled products from the shop. The normal Magento's delete product process in the admin does not work as it gets interrupted by the server (time exceeded on a shared host).

Also tried with product import and it's deleted option, but that too gets interrupted. Also tried Magmi with the same result. The problem might be related to reindexing that takes a long time, but not sure.

Just wondering if there is a safe way to delete all disabled products via SQL? Or would there be another way to automatically delete products one by one?

Best Answer

I would actually try to do it via SQL because in Magento, relations are done with foreign keys and ON DELETE CASCADE, so that you don't end up with dead data. However, before such an action, always make a backup, and try on a test database first!

DELETE catalog_product_entity FROM catalog_product_entity e
    INNER JOIN catalog_product_entity_int v
    ON v.entity_id=e.entity_id
    AND v.attribute_id=(SELECT attribute_id FROM eav_attribute ON attribute_code='status')
    WHERE v.value=2

If you prefer using the Magento methods, you can use the core/iterator resource model to not iterate over the collection. This avoids loading all products at once in memory, as in the other answers:

    $collection = Mage::getModel('catalog/product')->getCollection()
       ->addAttributeToSelect('status')
       ->addAttributeToFilter('status', array('neq' => 1));

   Mage::getResourceModel('core/iterator')
    ->walk($collection->getSelect(),[function($args) {
        $productId = $args['row']['entity_id'];
        $product = Mage::getModel('catalog/product')->setId($productId);
        $product->delete();
    }]);

The iterator fetches the db result row by row, without instantiating Magento models. To delete a product, you don't need to load it, just instantiate a model, set the id, and call delete().

Related Topic