Bulk Deleting Customers and Products in Magento (SQL Approach)

Managing large Magento stores can sometimes mean dealing with thousands of customers and products. When you need to reset your store or clean up data quickly, deleting records manually from the admin panel is not practical.

In such cases, using direct SQL queries is the fastest and most efficient approach.

⚠️ Important: Always take a full database backup before running these queries. These operations are destructive and cannot be undone.

Deleting All Customers

Magento stores customer data across multiple EAV tables. To completely remove all customers, you need to truncate all related tables and reset their auto-increment values.

SET FOREIGN_KEY_CHECKS=0;

-- Customer data
TRUNCATE customer_address_entity;
TRUNCATE customer_address_entity_datetime;
TRUNCATE customer_address_entity_decimal;
TRUNCATE customer_address_entity_int;
TRUNCATE customer_address_entity_text;
TRUNCATE customer_address_entity_varchar;

TRUNCATE customer_entity;
TRUNCATE customer_entity_datetime;
TRUNCATE customer_entity_decimal;
TRUNCATE customer_entity_int;
TRUNCATE customer_entity_text;
TRUNCATE customer_entity_varchar;

-- Logs
TRUNCATE log_customer;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;

-- Reset auto increment
ALTER TABLE customer_address_entity AUTO_INCREMENT=1;
ALTER TABLE customer_entity AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

SET FOREIGN_KEY_CHECKS=0;

-- Customer data
TRUNCATE customer_address_entity;
TRUNCATE customer_address_entity_datetime;
TRUNCATE customer_address_entity_decimal;
TRUNCATE customer_address_entity_int;
TRUNCATE customer_address_entity_text;
TRUNCATE customer_address_entity_varchar;

TRUNCATE customer_entity;
TRUNCATE customer_entity_datetime;
TRUNCATE customer_entity_decimal;
TRUNCATE customer_entity_int;
TRUNCATE customer_entity_text;
TRUNCATE customer_entity_varchar;

-- Logs
TRUNCATE log_customer;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;

-- Reset auto increment
ALTER TABLE customer_address_entity AUTO_INCREMENT=1;
ALTER TABLE customer_entity AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

Deleting All Products

Product data in Magento is even more distributed due to its flexible EAV structure. The following queries remove all product-related data and rebuild essential base records.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE catalog_product_bundle_option;
TRUNCATE catalog_product_bundle_option_value;
TRUNCATE catalog_product_bundle_selection;

TRUNCATE catalog_product_entity_datetime;
TRUNCATE catalog_product_entity_decimal;
TRUNCATE catalog_product_entity_gallery;
TRUNCATE catalog_product_entity_int;
TRUNCATE catalog_product_entity_media_gallery;
TRUNCATE catalog_product_entity_media_gallery_value;
TRUNCATE catalog_product_entity_text;
TRUNCATE catalog_product_entity_tier_price;
TRUNCATE catalog_product_entity_varchar;

TRUNCATE catalog_product_flat_1;
TRUNCATE catalog_product_link;
TRUNCATE catalog_product_link_attribute;
TRUNCATE catalog_product_link_attribute_decimal;
TRUNCATE catalog_product_link_attribute_int;
TRUNCATE catalog_product_link_attribute_varchar;
TRUNCATE catalog_product_link_type;

TRUNCATE catalog_product_option;
TRUNCATE catalog_product_option_price;
TRUNCATE catalog_product_option_title;
TRUNCATE catalog_product_option_type_price;
TRUNCATE catalog_product_option_type_title;
TRUNCATE catalog_product_option_type_value;

TRUNCATE catalog_product_super_attribute_label;
TRUNCATE catalog_product_super_attribute_pricing;
TRUNCATE catalog_product_super_attribute;
TRUNCATE catalog_product_super_link;

TRUNCATE catalog_product_enabled_index;
TRUNCATE catalog_product_website;
TRUNCATE catalog_product_relation;

TRUNCATE catalog_category_product_index;
TRUNCATE catalog_category_product;

TRUNCATE cataloginventory_stock_item;
TRUNCATE cataloginventory_stock_status;
TRUNCATE cataloginventory_stock_status_idx;
TRUNCATE cataloginventory_stock;

TRUNCATE core_url_rewrite;

-- Restore required defaults
INSERT INTO catalog_product_link_type (link_type_id, code) VALUES
(1,'relation'),
(2,'bundle'),
(3,'super'),
(4,'up_sell'),
(5,'cross_sell');

INSERT INTO catalog_product_link_attribute (product_link_attribute_id, link_type_id, product_link_attribute_code, data_type) VALUES
(1,2,'qty','decimal'),
(2,1,'position','int'),
(3,4,'position','int'),
(4,5,'position','int'),
(6,1,'qty','decimal'),
(7,3,'position','int'),
(8,3,'qty','decimal');

INSERT INTO cataloginventory_stock (stock_id, stock_name) VALUES
(1,'Default');

TRUNCATE catalog_product_entity;

SET FOREIGN_KEY_CHECKS = 1;

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE catalog_product_bundle_option;
TRUNCATE catalog_product_bundle_option_value;
TRUNCATE catalog_product_bundle_selection;

TRUNCATE catalog_product_entity_datetime;
TRUNCATE catalog_product_entity_decimal;
TRUNCATE catalog_product_entity_gallery;
TRUNCATE catalog_product_entity_int;
TRUNCATE catalog_product_entity_media_gallery;
TRUNCATE catalog_product_entity_media_gallery_value;
TRUNCATE catalog_product_entity_text;
TRUNCATE catalog_product_entity_tier_price;
TRUNCATE catalog_product_entity_varchar;

TRUNCATE catalog_product_flat_1;
TRUNCATE catalog_product_link;
TRUNCATE catalog_product_link_attribute;
TRUNCATE catalog_product_link_attribute_decimal;
TRUNCATE catalog_product_link_attribute_int;
TRUNCATE catalog_product_link_attribute_varchar;
TRUNCATE catalog_product_link_type;

TRUNCATE catalog_product_option;
TRUNCATE catalog_product_option_price;
TRUNCATE catalog_product_option_title;
TRUNCATE catalog_product_option_type_price;
TRUNCATE catalog_product_option_type_title;
TRUNCATE catalog_product_option_type_value;

TRUNCATE catalog_product_super_attribute_label;
TRUNCATE catalog_product_super_attribute_pricing;
TRUNCATE catalog_product_super_attribute;
TRUNCATE catalog_product_super_link;

TRUNCATE catalog_product_enabled_index;
TRUNCATE catalog_product_website;
TRUNCATE catalog_product_relation;

TRUNCATE catalog_category_product_index;
TRUNCATE catalog_category_product;

TRUNCATE cataloginventory_stock_item;
TRUNCATE cataloginventory_stock_status;
TRUNCATE cataloginventory_stock_status_idx;
TRUNCATE cataloginventory_stock;

TRUNCATE core_url_rewrite;

-- Restore required defaults
INSERT INTO catalog_product_link_type (link_type_id, code) VALUES
(1,'relation'),
(2,'bundle'),
(3,'super'),
(4,'up_sell'),
(5,'cross_sell');

INSERT INTO catalog_product_link_attribute (product_link_attribute_id, link_type_id, product_link_attribute_code, data_type) VALUES
(1,2,'qty','decimal'),
(2,1,'position','int'),
(3,4,'position','int'),
(4,5,'position','int'),
(6,1,'qty','decimal'),
(7,3,'position','int'),
(8,3,'qty','decimal');

INSERT INTO cataloginventory_stock (stock_id, stock_name) VALUES
(1,'Default');

TRUNCATE catalog_product_entity;

SET FOREIGN_KEY_CHECKS = 1;

Mass Enable or Disable Product Gallery Images

If you need to quickly toggle visibility of all product images:

-- Enable all images
UPDATE catalog_product_entity_media_gallery_value
SET disabled = 0
WHERE disabled = 1;

-- Disable all images
UPDATE catalog_product_entity_media_gallery_value
SET disabled = 1
WHERE disabled = 0;

-- Enable all images
UPDATE catalog_product_entity_media_gallery_value
SET disabled = 0
WHERE disabled = 1;

-- Disable all images
UPDATE catalog_product_entity_media_gallery_value
SET disabled = 1
WHERE disabled = 0;

When to Use This Approach

This method is useful when:

Resetting a development or staging environment
Preparing a clean database for testing
Migrating or rebuilding a store
Removing bulk demo or corrupted data

Final Thoughts

Magento’s EAV structure makes it powerful but also complex when performing bulk operations. While SQL gives you full control and speed, it also comes with risk.

Use it carefully, always back up first, and ideally run these operations in a staging environment before applying them to production.

For long-term maintainability, consider using Magento CLI tools or scripts where possible, but for full resets, SQL remains the most direct and effective solution.