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;