Magento doesn’t provide an easy way to generate a large number of SKUs. I wanted to post a (primitive) script I created based off Ryan Anthony’s gist. This script is useful for benchmarking performance or testing features that might be affected by a large catalog.
The below script is a stored procedure that must be installed on your database before invoking it.
After installing the procedure, you must invoke it by running:
call build_catalog(10,100000);
# First argument is number of categories
# Second argument is number of products
# Update for Magento 2:
# Install as stored procedure
create procedure build_catalog(IN categories INT, IN products INT)
begin
SET @category_count = 1;
SET @CATNAMEPREFIX = "Category ";
SET @CATURLKEYPREFIX = "cat-";
SET @CATURLPATHPREFIX = "catpath-";
SET @ROOTCATEGORY = 2;
SET @INCLUDEINMENU = 1;
SET @ISACTIVE = 1;
SELECT @category_entity_type_id := entity_type_id from eav_entity_type where entity_type_code = 'catalog_category';
SELECT @category_attribute_set_id := attribute_set_id from eav_attribute_set where attribute_set_name = 'Default' and entity_type_id = @category_entity_type_id;
SELECT @include_in_menu := attribute_id from eav_attribute where attribute_code = 'include_in_menu' and entity_type_id = @category_entity_type_id;
SELECT @is_active := attribute_id from eav_attribute where attribute_code = 'is_active' and entity_type_id = @category_entity_type_id;
SELECT @category_name_id := attribute_id from eav_attribute where attribute_code = 'name' and entity_type_id = @category_entity_type_id;
SELECT @category_url_key_id := attribute_id from eav_attribute where attribute_code = 'url_key' and entity_type_id = @category_entity_type_id;
SELECT @category_url_path_id := attribute_id from eav_attribute where attribute_code = 'url_path' and entity_type_id = @category_entity_type_id;
WHILE @category_count < categories DO
INSERT INTO catalog_category_entity (attribute_set_id,parent_id,created_at,updated_at,path,position,level,children_count) VALUES (@category_attribute_set_id,@ROOTCATEGORY,NOW(),NOW(),concat("1/2/", @category_entity_id),1,2,0);
SET @category_entity_id = LAST_INSERT_ID();
INSERT INTO catalog_category_entity_int (attribute_id,entity_id,value) VALUES(@include_in_menu,@category_entity_id,@INCLUDEINMENU);
INSERT INTO catalog_category_entity_int (attribute_id,entity_id,value) VALUES(@is_active,@category_entity_id,@ISACTIVE);
INSERT INTO catalog_category_entity_varchar (attribute_id,entity_id,value) VALUES(@category_url_key_id,@category_entity_id,concat(@CATURLKEYPREFIX, @category_count));
INSERT INTO catalog_category_entity_varchar (attribute_id,entity_id,value) VALUES(@category_url_path_id,@category_entity_id,concat(@CATURLPATHPREFIX, @category_count));
INSERT INTO catalog_category_entity_varchar (attribute_id,entity_id,value) VALUES(@category_name_id,@category_entity_id,concat(@CATNAMEPREFIX, @category_count));
SET @category_count = @category_count + 1;
END WHILE;
SET @product_count = 1;
SET @NAMEPREFIX = "Test Product ";
SET @URLKEYPREFIX = "key-";
SET @URLPATHPREFIX = "path-";
SET @SKUPREFIX = "sku-";
SET @VISIBILITY = 4;
SET @STATUS = 1;
SET @TAXCLASS = 2;
SET @MAXPRICE = 100;
SET @MAXWEIGHT = 20;
SET @WEBSITE = 1;
SET @STOREID = 1;
SET @QTY = 999;
SET @DESCRIPTION = "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.";
SET @SHORTDESCRIPTION = "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.";
SELECT @entity_type_id := entity_type_id from eav_entity_type where entity_type_code = 'catalog_product';
SELECT @attribute_set_id := attribute_set_id from eav_attribute_set where attribute_set_name = 'Default' and entity_type_id = @entity_type_id;
SELECT @visibility_id := attribute_id from eav_attribute where attribute_code = 'visibility' and entity_type_id = @entity_type_id;
SELECT @status_id := attribute_id from eav_attribute where attribute_code = 'status' and entity_type_id = @entity_type_id;
SELECT @taxclass_id := attribute_id from eav_attribute where attribute_code = 'tax_class_id' and entity_type_id = @entity_type_id;
SELECT @description_id := attribute_id from eav_attribute where attribute_code = 'description' and entity_type_id = @entity_type_id;
SELECT @short_description_id := attribute_id from eav_attribute where attribute_code = 'short_description' and entity_type_id = @entity_type_id;
SELECT @price_id := attribute_id from eav_attribute where attribute_code = 'price' and entity_type_id = @entity_type_id;
SELECT @weight_id := attribute_id from eav_attribute where attribute_code = 'weight' and entity_type_id = @entity_type_id;
SELECT @name_id := attribute_id from eav_attribute where attribute_code = 'name' and entity_type_id = @entity_type_id;
SELECT @url_key_id := attribute_id from eav_attribute where attribute_code = 'url_key' and entity_type_id = @entity_type_id;
SELECT @url_path_id := attribute_id from eav_attribute where attribute_code = 'url_path' and entity_type_id = @entity_type_id;
WHILE @product_count < products DO
INSERT INTO catalog_product_entity (attribute_set_id,type_id,sku,created_at,updated_at) VALUES (@attribute_set_id,"simple",concat(@SKUPREFIX, @product_count),NOW(),NOW());
SET @entity_id = LAST_INSERT_ID();
INSERT INTO catalog_product_entity_int (attribute_id,entity_id,value) VALUES(@visibility_id,@entity_id,@VISIBILITY);
INSERT INTO catalog_product_entity_int (attribute_id,entity_id,value) VALUES(@status_id,@entity_id,@STATUS);
INSERT INTO catalog_product_entity_int (attribute_id,entity_id,value) VALUES(@taxclass_id,@entity_id,@TAXCLASS);
INSERT INTO catalog_product_entity_text (attribute_id,entity_id,value) VALUES(@description_id,@entity_id,@DESCRIPTION);
INSERT INTO catalog_product_entity_text (attribute_id,entity_id,value) VALUES(@short_description_id,@entity_id,@SHORTDESCRIPTION);
INSERT INTO catalog_product_entity_decimal (attribute_id,entity_id,value) VALUES(@price_id,@entity_id,ROUND(RAND() * @MAXPRICE,2));
INSERT INTO catalog_product_entity_decimal (attribute_id,entity_id,value) VALUES(@weight_id,@entity_id,ROUND(RAND() * @MAXWEIGHT,2));
INSERT INTO catalog_product_entity_varchar (attribute_id,entity_id,value) VALUES(@url_key_id,@entity_id,concat(@URLKEYPREFIX, @product_count));
INSERT INTO catalog_product_entity_varchar (attribute_id,store_id,entity_id,value) VALUES(@url_path_id,@STOREID,@entity_id,concat(@URLPATHPREFIX, @product_count));
INSERT INTO catalog_product_entity_varchar (attribute_id,entity_id,value) VALUES(@url_path_id,@entity_id,concat(@URLPATHPREFIX, @product_count));
INSERT INTO catalog_product_entity_varchar (attribute_id,entity_id,value) VALUES(@name_id,@entity_id,concat(@NAMEPREFIX, @product_count));
INSERT INTO catalog_category_product (category_id,product_id,position) VALUES((SELECT entity_id FROM catalog_category_entity WHERE parent_id = @ROOTCATEGORY ORDER BY RAND() LIMIT 1),@entity_id,1);
SET @last_id = LAST_INSERT_ID();
INSERT INTO catalog_category_product_index (category_id,product_id,position,is_parent,visibility) VALUES(@last_id,@entity_id,1,1,4);
INSERT INTO cataloginventory_stock_item (product_id,stock_id,qty,is_in_stock) VALUES (@entity_id,1,@QTY,1);
INSERT INTO cataloginventory_stock_status (product_id,website_id,stock_id,qty,stock_status) VALUES (@entity_id,@WEBSITE,1,@QTY,1);
INSERT INTO catalog_product_website (product_id,website_id) VALUES (@entity_id,@WEBSITE);
SET @product_count = @product_count + 1;
END WHILE;
end;
And a script to delete from relevant tables: (This lets you “start fresh”)
# Make sure to exclude root categories or category screen won't work
delete from catalog_category_entity where entity_id NOT IN (1,2);
delete from catalog_category_entity_int where entity_id NOT IN (1,2);
delete from catalog_category_entity_varchar where entity_id NOT IN (1,2);
delete from catalog_category_entity_decimal where entity_id NOT IN (1,2);
delete from catalog_category_entity_datetime where entity_id NOT IN (1,2);
delete from catalog_category_entity_text where entity_id NOT IN (1,2);
delete from catalog_product_entity;
delete from catalog_product_entity_int;
delete from catalog_product_entity_datetime;
delete from catalog_product_entity_decimal;
delete from catalog_product_entity_text;
delete from catalog_product_entity_varchar;
delete from catalog_category_product_index;
delete from cataloginventory_stock_item;
delete from cataloginventory_stock_status;
delete from catalog_product_website;
