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)