Our brands

How To Clear Magento 2 Database For Faster Performance

how-to-clear-magento-database

You don’t know how to clear magento 2 database you have made in your magento theme? But you may wish delete all database so that your site can start over again

Unfortunately Magento doesn’t support you to delete those. You must love to have “Delete selected Orders” button in Magento Admin Panel, however, there is not functionality.

Hence, in this post we will show you how to clear magento 2 database such as:

  • Clearing all products
  • Clearing all reviews
  • Clearing all customers
  • Clearing all attributes
  • Clearing all attribute sets
  • Clearing all categories
  • Clearing all search results
  • Clearing all orders, shipments, transactions etc.
  • Reset all ID counters

Let’s getting started

1. Clearing all products


SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `catalog_product_entity`;
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`,`website_id`,`stock_name`) VALUES (1,0,'Default');

2. Clearing all reviews


SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `rating_option_vote`;
TRUNCATE TABLE `rating_option_vote_aggregated`;
TRUNCATE TABLE `review`;
TRUNCATE TABLE `review_detail`;
TRUNCATE TABLE `review_entity_summary`;
TRUNCATE TABLE `review_store`;

3. Clearing all categories


SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;
INSERT INTO `catalog_category_entity` (`entity_id`, `attribute_set_id`, `parent_id`, `created_at`, `updated_at`, `path`, `position`, `level`, `children_count`) VALUES
(1, 0, 0, '2016-05-09 08:03:52', '2016-05-09 08:03:52', '1', 0, 0, 1),
(2, 3, 1, '2016-05-09 08:03:52', '2016-05-09 08:03:52', '1/2', 1, 1, 0);

4. Clearing all customers


SET FOREIGN_KEY_CHECKS = 0;
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`;

ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;

5. Clearing all orders, shipments, transactions etc.


SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE `sales_order`;
TRUNCATE `sendfriend_log`;
TRUNCATE `wishlist`;
TRUNCATE `report_event`;

ALTER TABLE `sales_order` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;

6. Clearing all search results

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalogsearch_fulltext_scope1`;
TRUNCATE TABLE `catalogsearch_fulltext_scope2`;
TRUNCATE TABLE `search_query`;

 

Now, let’s Reset all ID counters


TRUNCATE `eav_entity_store`;
ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1;

We hope our “clear magento 2 database” tutorial is helpful for you and don’t forget to share your idea or ask us if you have any question

If you still confuse about this Tutorials, please feel free to contact our professional support team with more than 7 years of experiences for more helpful aid.

All questions are always welcomed. Contact us now! Magento Development Services

Magento 2 Themes and Extensions

*** GET ACCESS TO 60+ MAGENTO THEMES FOR ONLY $84
*** Magento 2 Page Builder
*** Drag & Drop Magento 2 Mega Menu
*** Visual Magento 2 Form Builder
*** SEO optimized Magento 2 Blog Extension
*** Magento 2 Brand Extension

Share this Post!

Alena Dao
Alena Dao
“Good marketing makes the company look smart. Great marketing makes the customer feel smart.”

2 Comments

  1. Avatar
    Max May 28, 2017 at 2:17 pm - Reply

    Will you be kind enough to guide how to run this command. Thanks

    • Avatar
      Radosav Brajic June 21, 2017 at 9:38 pm - Reply

      Run it from MySQL
      You can do this via a terminal by running the command:
      mysql -h{magento_database_host_server} -u{magento_database_user} -p{magento_database_password} {magento_database_name}

      You can get all these variables from app/etc/env.php

      The command will open the mysql monitor, you can enter the commands from the tutorial there.

Leave a Comment

Your email address will not be published.

Do NOT follow this link or you will be banned from the site!