Magento 2: Keep 5000 Products with Mixed Visibilities and Delete the Rest

This guide provides the exact SQL and CLI commands to retain 5000 products with a balanced distribution of visibility values, and remove all others safely.

Step 1: Create a Permanent Table for Products to Keep

CREATE TABLE products_to_keep (entity_id INT);

Step 2: Insert the 5000 Products to Keep

INSERT INTO products_to_keep
SELECT * FROM (
    (
        SELECT e.entity_id FROM catalog_product_entity e
        JOIN catalog_product_entity_int v ON v.entity_id = e.entity_id
            AND v.attribute_id = (
                SELECT attribute_id FROM eav_attribute 
                WHERE attribute_code = 'visibility' AND entity_type_id = 4
            )
            AND v.store_id = 0
        WHERE v.value = 1
        LIMIT 1000
    )
    UNION ALL
    (
        SELECT e.entity_id FROM catalog_product_entity e
        JOIN catalog_product_entity_int v ON v.entity_id = e.entity_id
            AND v.attribute_id = (
                SELECT attribute_id FROM eav_attribute 
                WHERE attribute_code = 'visibility' AND entity_type_id = 4
            )
            AND v.store_id = 0
        WHERE v.value = 2
        LIMIT 1
    )
    UNION ALL
    (
        SELECT e.entity_id FROM catalog_product_entity e
        JOIN catalog_product_entity_int v ON v.entity_id = e.entity_id
            AND v.attribute_id = (
                SELECT attribute_id FROM eav_attribute 
                WHERE attribute_code = 'visibility' AND entity_type_id = 4
            )
            AND v.store_id = 0
        WHERE v.value = 3
        LIMIT 999
    )
    UNION ALL
    (
        SELECT e.entity_id FROM catalog_product_entity e
        JOIN catalog_product_entity_int v ON v.entity_id = e.entity_id
            AND v.attribute_id = (
                SELECT attribute_id FROM eav_attribute 
                WHERE attribute_code = 'visibility' AND entity_type_id = 4
            )
            AND v.store_id = 0
        WHERE v.value = 4
        LIMIT 3000
    )
) AS combined;

Step 3: Verify the Keep List

SELECT COUNT(*) FROM products_to_keep;

Step 4: Preview the Impact

SELECT COUNT(*) as to_delete
FROM catalog_product_entity
WHERE entity_id NOT IN (SELECT entity_id FROM products_to_keep);
SELECT COUNT(*) as to_keep
FROM catalog_product_entity
WHERE entity_id IN (SELECT entity_id FROM products_to_keep);

Step 5: Delete All Other Products (Batch Mode)

DELETE FROM catalog_product_entity 
WHERE entity_id NOT IN (SELECT entity_id FROM products_to_keep)
LIMIT 1000;

Alternative: Magento CLI Deletion

bin/magento catalog:product:delete --ids=$(mysql -u USER -pPASS DBNAME -se "SELECT GROUP_CONCAT(entity_id) FROM catalog_product_entity WHERE entity_id NOT IN (SELECT entity_id FROM products_to_keep)")