1

I've looked up which tables I can clear for Magento to perform better with Database requests, everyone so far is saying that the following are the best:

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

But, my only problem is, I'm using Google Analytics, and I cleared these before and lost a lot of tracking data for GA, not just that but I had to force Magento to re-index everything.

Which tables are the ones that control indexing of URL's? And which ones are the safest to clear without affecting my GA stats?

1
  • 1
    These logs don't affect GA in any way. GA tracks using cookies dropped on the viewers' browser. I'm not convinced truncating these logs is the cause of a drop in GA results. – Craig Sep 7 '16 at 13:14
1

Core URL Rewrite

The URLs are stored in core_url_rewrite. Depend of how your Store is configured, you can delete a lot of URLS. For example, if you have disabled the category Path in the Product URL, you can delete als url_rewrites with the path. A better soloution would be to create an observer an blocking the creation of all these urls.

Quotes

You can delete old entries from sales_flat_quote. If you have an old shop, these Table is very big. Also it will automaticlly delete the associated entries in sales_flat_quote_address, sales_flat_quote_address_item, sales_flat_quote_item, sales_flat_quote_item_option, sales_flat_quote_payment and sales_flat_quote_shipping_rate.
You can delete old entries with this Query:

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

You can also create an Cronjobs which deletes old quotes automaticly.

Newsletter

You can delete old newsletter subscription, which are never confirmed, too. The Problem is, that in the subscriber table newsletter_subscriber isn't a date. But you can figure out what customer subscribed 60 Days ago and delete all IDs lower then this:

DELETE FROM newsletter_subscriber WHERE subscriber_id < 13263346

Coupon Codes

If you have a lot coupon code, you can delete old one, too. You can do it in the adminpanel. Just sort the grid by creation date.
With this, you clean up this tables: salesrule, salesrule_coupon, salesrule_coupon_usage, salesrule_customer, salesrule_customer_group, salesrule_label, salesrule_product_attribute, salesrule_website

Third-Party

A lot of third Party modules create sometime log tables or save a lot of data in it. You have to figure it out by yourself.
To find big tables, you can use this Query:

SELECT 
    table_name AS `Table Name`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` 
FROM information_schema.TABLES 
WHERE table_schema = "YOUR_DATABASE_NAME"
ORDER BY `Size (MB)`  DESC

The Database isn't connected to your GA. So, you don't lose Data in GA if you delete tables.

Tip

You don't have to flush the whole Table. You have to understand what data are stored in it and you can just delete old entries

2
  • Unfortunately this doesn't help with inno db tables, because they only mark entries as deleted. To decrease the size of an inno db table you have to export and import it again :-/ The alternative is TRUNCATE ;-) – Fabian Blechschmidt Sep 8 '16 at 22:12
  • 1
    If I get it right, you can also use OPTIMIZE TABLE sales_flat_quote; – Pawel Sep 9 '16 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.