Enourmous Magento sales_flat_quote* tables
Recently, we have run into a few stores where the sales quote tables have grown beyond all proportion and resulted in the store grinding to a halt.
The worst that we have seen so far is a Magento Enterprise installation that had 17 million entries, consuming a huge amount of space and locking dozens of rows.
If you have 17 million records, then trying to run a truncate older than X
will take an extremely significant time, so we had to result to something a little more extreme - we do not advise the following unless you have exhausted the other option.
The aggressive way
SET FOREIGN_KEY_CHECKS=0;
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;
SET FOREIGN_KEY_CHECKS=1;
The better way
DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY)
And for those Magento Enterprise users
DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY)
DELETE FROM enterprise_customer_sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY)