List of tables to safely truncate in Magento?
Before you do anything
- Make sure you test clearing this data in a non-production environment first.
- Always make backups before you lose data forever.
- Make sure you're
truncateing, notdroping. - Probably a good idea to reindex everything via shell after mass deleting records
Update:
You can use this n98-magerun module to clean up your tables.
Or do it manually by following instructions below.
To expand on Jim's answer, Magento Support doesn't need the contents of these tables when they ask for a copy of your DB, so you could consider them non-essential.
Cache tables
core_cache
core_cache_tag
Cache data is temporary. Clearing these should be safe.
Session tables
core_session
No need to keep year old sessions. New sessions will automatically be created (though it will cause people to be logged out/break a current checkout flow).
Dataflow tables
dataflow_batch_export
dataflow_batch_import
There are essentially logs of each time a batch is run and not critical.
Admin logs
enterprise_logging_event
enterprise_logging_event_changes
These are logs of which admins are doing what in the backend. Very nice for tracking down "who broke what" but don't need to be kept forever. You can safely truncate these.
Pro-tip: Make sure you're cleaning out old records in System > Configuration > Advanced > System > Admin Actions Log Archiving
Support tables
enterprise_support_backup
enterprise_support_backup_item
History of support from Magento, may or may not exist for you.
Index tables
index_event
index_process_event
A back log of index entries that need to be updated. However, they don't delete themselves once they're obsolete.
Log tables
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
log_visitor_online
Log data, mostly unused. However, I've seen "Sort by Most Viewed" modules use the log_visitor_info table so be cautious.
Pro-tip: Make sure you're cleaning out old records in System > Configuration > Advanced > System > Log Cleaning (this only does visitors, customers, and urls)
Report tables
report_event
report_viewed_product_index
These are aggregated tables that can be rebuilt when running reports.
Other tables that can use a pruning once in a while are
Quote tables
sales_flat_quote
sales_flat_quote_address
sales_flat_quote_address_item
sales_flat_quote_item
sales_flat_quote_item_option
sales_flat_quote_payment
sales_flat_quote_shipping_rate
If having 3 year old abandoned cart data isn't important to you, consider truncating these. Keep in mind that current carts are in here, so schedule this during off hours or remove rows with updated_at older than X days.
Pro-tip: install Aoe_QuoteCleaner
Staging tables
If you use Enterprise's staging feature, you might start seeing tables with the s_ prefix. There is no clean up for these once the staging site is removed. If your enterprise_staging table is empty, you don't need these tables anymore.
Changelog tables
catalog_category_flat_cl
catalog_category_product_cat_cl
catalog_category_product_index_cl
catalog_product_flat_cl
catalog_product_index_price_cl
cataloginventory_stock_status_cl
catalogsearch_fulltext_cl
enterprise_url_rewrite_category_cl
enterprise_url_rewrite_product_cl
enterprise_url_rewrite_redirect_cl
Magento introduced MySQL triggers that write to change log tables when certain tables' data is modified. Later the scheduler indexer picks up the change log entries and updates the items. However, it doesn't clean up when it's done. You can clear these out from time to time.
Category and product flat tables
catalog_category_flat_store_1
catalog_category_flat_store_2
catalog_category_flat_store_3
catalog_category_flat_store_4
catalog_category_flat_store_5
catalog_category_flat_store_6
catalog_category_flat_store_7
catalog_product_flat_1
catalog_product_flat_2
catalog_product_flat_3
catalog_product_flat_4
catalog_product_flat_5
catalog_product_flat_6
catalog_product_flat_7
These tables I tend to drop. After a reindex they will re-create themselves. In some cases store 7 might not exist anymore but you still have the dead flat table.
URL rewrite tables
Be careful here, you may not want to truncate all of these.
core_url_rewrite
enterprise_url_rewrite
First check for any records that are is_system = 0. If so you won't want to truncate, you'll lose custom redirects. Try DELETE FROM core_url_rewrite WHERE is_system = 1 instead. Reindexing rewrites will re-populate this table with the rest.
More report tables
report_viewed_product_aggregated_daily
report_viewed_product_aggregated_monthly
report_viewed_product_aggregated_yearly
These are aggregated and can be rebuilt (like indexes).
When you log an issue with Magento support and they ask you to provide a database dump, the script they give you dumps the schema only for the following tables:
core_cache
core_cache_option
core_cache_tag
core_session
dataflow_batch_export
dataflow_batch_import
enterprise_logging_event
enterprise_logging_event_changes
enterprise_support_backup
enterprise_support_backup_item
index_event
index_process_event
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
log_visitor_online
report_event
report_viewed_product_index
If Magento support doesn't need the contents of these tables to resolve issues, it would be a safe assumption that they can be safely truncated.
The catalog_product_flat_* tables and catalog_category_flat_* tables can also be truncated as a reindex will re-populate them.
A user can add entries to the core_url_rewrite table manually from the back end and I wouldn't like to guarantee that two products pr categories with identical URL keys will always have the same URLs after truncating core_url_rewrite. It's not one I'd rely on being able to truncate safely.
I want to add to the list that you can also truncate "catalogrule_product" and "catalogrule_product_price". You can regenerate it by running Apply Rules in Pormos > Catalog Rules. I have truncated this table quite a few times to know that it's safe. NB! All your catalog rule prices will disappear from the frontend until you re-run the rules.
I would also love to see if someone can describe what happens with the site if these tables are cleared. E.g. I assume that dropping core_session (if we are using database to store those) will drop all customer current "logged in" sessions, will it also drop carts of guests?