Truncate and clear logs in Magento

Run the following in MYSQL to reduce your Magento database, by possibly hundreds of megabytes. It clears the logs.

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 report_event;
truncate report_viewed_product_index;
truncate report_compared_product_index;
truncate catalog_compare_item;
truncate dataflow_batch_export;
truncate dataflow_batch_import;

// clear the URL rewrite table which can grow huge - afterwards you will need to run: php -f indexer.php --reindex catalog_url

DELETE FROM core_url_rewrite WHERE is_system = 1;

// You can clear these 'quote' tables too :

truncate enterprise_customer_sales_flat_quote;
truncate enterprise_customer_sales_flat_quote_address;
truncate sales_flat_quote;
truncate sales_flat_quote_address;
truncate sales_flat_quote_shipping_rate;

OR if you're worried about time :

DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY);

DELETE FROM sales_flat_quote_address WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY);

DELETE FROM sales_flat_quote_shipping_rate WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY);

The "catalogsearch_result" can grow huge, so to trim this, you need to truncate and then do an update :

TRUNCATE catalogsearch_result
UPDATE catalogsearch_query SET is_processed=0

Remove old index_events :

DELETE FROM index_event WHERE created_at < "2018-01-01"

Leave a Reply