Warning: Your browser is very out-of-date. You should upgrade to a better browser.

This is a simple list of SQL commands which can be used to clear out various parts of the Magento database. It’ll be useful when creating a development environment from an existing database. It will clear all real customer, orders and sales data while keeping the catalogue and CMS information.

The basic command should be to disable foreign key checks in MySQL, followed by the required truncate commands, then reenable foreign key checks.

SET FOREIGN_KEY_CHECKS=0; 

Clear the log tables down

This should be managed by Magento but issues with Cron scheduling can mean they grow way beyond a reasonable size. Clearing the log tables will have no impact on the system.

TRUNCATE `log_customer`; 
TRUNCATE `log_quote`; 
TRUNCATE `log_summary`; 
TRUNCATE `log_summary_type`; 
TRUNCATE `log_url`; 
TRUNCATE `log_url_info`; 
TRUNCATE `log_visitor`; 
TRUNCATE `log_visitor_info`; 
TRUNCATE `log_visitor_online`; 

Clear out report tables

The report tables will, again, have no negative impact on your store but it will affect performance of some features including recently viewed products, and any reporting you do will need rebuilt before run again.

TRUNCATE `coupon_aggregated`;
TRUNCATE `coupon_aggregated_order`;
TRUNCATE `coupon_aggregated_updated`;
TRUNCATE `report_viewed_product_aggregated_daily`;
TRUNCATE `report_viewed_product_aggregated_monthly`;
TRUNCATE `report_viewed_product_aggregated_yearly`;
TRUNCATE `sales_bestsellers_aggregated_daily`;
TRUNCATE `sales_bestsellers_aggregated_monthly`;
TRUNCATE `sales_bestsellers_aggregated_yearly`;
TRUNCATE `sales_invoiced_aggregated`;
TRUNCATE `sales_invoiced_aggregated_order`;
TRUNCATE `sales_order_aggregated_created`;
TRUNCATE `sales_order_aggregated_updated`;
TRUNCATE `sales_refunded_aggregated`;
TRUNCATE `sales_refunded_aggregated_order`;
TRUNCATE `sales_shipping_aggregated`;
TRUNCATE `sales_shipping_aggregated_order`;
TRUNCATE `tax_order_aggregated_created`;
TRUNCATE `tax_order_aggregated_updated`;

Miscellaneous

TRUNCATE `catalogsearch_query`;
TRUNCATE `index_event`; 
TRUNCATE `index_process_event`;   
TRUNCATE `report_event`;
TRUNCATE `report_viewed_product_index`;
TRUNCATE `sendfriend_log`; 
TRUNCATE `tag`; 
TRUNCATE `tag_relation`; 
TRUNCATE `tag_summary`; 
TRUNCATE `wishlist`;

Remove customer information

-- Customerc
TRUNCATE `customer_entity`;
TRUNCATE `customer_entity_datetime`;
TRUNCATE `customer_entity_decimal`;
TRUNCATE `customer_entity_int`;
TRUNCATE `customer_entity_text`;
TRUNCATE `customer_entity_varchar`;

-- Customer Addresses
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`;

Sales Quotes and Order Data

-- Quotes
TRUNCATE `sales_flat_quote`; 
TRUNCATE `sales_flat_quote_address`; 
TRUNCATE `sales_flat_quote_address_item`; 
TRUNCATE `sales_flat_quote_item`; 
TRUNCATE `sales_flat_quote_item_option`; 
TRUNCATE `sales_flat_quote_payment`; 
TRUNCATE `sales_flat_quote_shipping_rate`; 

-- Orders
TRUNCATE `sales_flat_order`; 
TRUNCATE `sales_flat_order_address`; 
TRUNCATE `sales_flat_order_grid`; 
TRUNCATE `sales_flat_order_item`; 
TRUNCATE `sales_flat_order_payment`; 
TRUNCATE `sales_flat_order_status_history`;

-- Invoices
TRUNCATE `sales_flat_invoice`;
TRUNCATE `sales_flat_invoice_comment`;
TRUNCATE `sales_flat_invoice_grid`;
TRUNCATE `sales_flat_invoice_item`;

-- Shipments
TRUNCATE `sales_flat_shipment`;
TRUNCATE `sales_flat_shipment_comment`;
TRUNCATE `sales_flat_shipment_grid`;
TRUNCATE `sales_flat_shipment_item`;
TRUNCATE `sales_flat_shipment_track`;

-- Sales Order Tax
TRUNCATE `sales_order_tax`;
TRUNCATE `sales_order_tax_item`;

-- Creditmemos
TRUNCATE `sales_flat_creditmemo`;
TRUNCATE `sales_flat_creditmemo_comment`;
TRUNCATE `sales_flat_creditmemo_grid`;
TRUNCATE `sales_flat_creditmemo_item`;

After each update, re-enable foreign key checks.

SET FOREIGN_KEY_CHECKS=1;

Magento

Magento is a feature-rich ecommerce platform built on open-source technology that provides online merchants with unprecedented flexibility and control over the look, content and functionality of their ecommerce store.

About Magento Ecommerce · Magento Posts

Related Magento Posts

Magento December 2021

Optimising Magento 2 in Easy Steps

Some (fairly) simple step-by-step instructions to ensure your Magento 2 store is running the best it can. While most of these options should only require...
Magento September 2021

The Future of Magento

The [Mage Open Source Community Alliance](https://www.mage-os.community/) have published an open letter to the Magento community to announce a new initiative designed to focus on ensuring...
Magento July 2021

Magento 2 Blog Extensions

While Magento isn't the go-to platform for blogs it can be useful for store owners to have a quick and easy way to post updated...

More Magento Posts...