- 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
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.
core_cache
core_cache_option
core_cache_tag
Cache data is temporary. Clearing these should be safe.
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_batch_export
dataflow_batch_import
There are essentially logs of each time a batch is run and not critical.
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-top: Make sure you're cleaning out old records in System > Configuration > Advanced > System > Admin Actions Log Archiving
enterprise_support_backup
enterprise_support_backup_item
History of support from Magento, may or may not exist for you.
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_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-top: Make sure you're cleaning out old records in System > Configuration > Advanced > System > Log Cleaning (this only does visitors, customers, and urls)
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
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
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.
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.
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.
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 = 0 instead. Reindexing rewrites will re-populate this table with the rest.
report_viewed_product_aggregated_daily
report_viewed_product_aggregated_monthly
report_viewed_product_aggregated_yearly
These are aggregated and can be rebuilt (like indexes).