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)