How to handle huge sales_flat_quote tables - remove abandoned cart refuse

Question

Situation

In our magento database we have three tables that are growing too big.

1. sales_flat_quote_shipping_rate: 3045MB
2. sales_flat_quote_address: 1688MB
3. sales_flat_quote: 1897MB

That's more than 6GB of data.

I've found this article on how to truncate these tables. This article only speaks of the third table.

I'm not sure about the truncating, and I prefer to delete all data older than 60 days. The article speaks of 17 million records - we have 20! If I'm correct, deleting these records won't lock the table, and I'm currently testing this on a test installation. That seems to work, and it doesn't conflict too much with the site.

Questions

• I would like to know if I can use the same query for the other two tables as well?
• I'm wondering what these tables are for.

Answer 1

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

sales_flat_quote and it's related tables use foreign key relations to delete on cascade so deletions in the primary table deletes all related entries automatically in the other tables.

Once you get it down in size, you can easily modify the observer (obligatory make a module here) that's supposed to clean out expired quotes to include all quotes as is done in the first link with customized retention for abandoned cart recovery and retention of logged in customer quotes for a reasonable period so your customers don't get mad when their cart empties unexpectedly: Are unconverted quote records ever removed in Magento?

or use Fabrizio Branca's AOE Quote Cleaner

sales_flat_quote* tables contain shopping cart contents. This is known as a quote and is held until the customer decides to finish placing the order. For finished quotes that have been converted to Sales-Order, Magento automatically clears them with a cron job. The logic was never extended to include open quotes.

Answer 2

Although Fiasco Labs's solution works, I would just start off with an extension right from the get-go and use Mage_Sales_Model_Quote_Resource_Collection to filter and remove older quotes of choosing. Using the proper methods, you can ensure your operation will work on any Magento installation by not relying on database constraints or raw queries.

Something like (untested)…

$collection = Mage::getModel('sales/quote')->getCollection() 
    -->addFieldToSelect('entity_id') // this is important
    -->addFieldToFilter('created_at', array('lt' => 'some_date'));

foreach ($collection as $quote) {
    $quote->delete(); // safely delete selected quote
}

You could extend this off of Mage_Log or make its own stand-alone extension. Pretty simple.

Answer 3

DELETE FROM sales_flat_quote WHERE entity_id NOT IN (SELECT quote_id FROM sales_flat_order);

This query will delete all quotes, that where not converted in orders.

Answer 4

In Magento2, the ‘sales_flat_quote’ was replaced with ‘quote’, so following SQL command can fix this issue for Magento2:

update quote set items_count = NULL

Answer 5

A client's database that had over 500K records in this table was causing a serious slowdown. Here was my approach.

Make backups!

No, seriously. Make backups using mysqldump.

Here's What I Did - After Making A Backup

1. Find the tables that reference sales_flat_quote.

mysql> SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'sales_flat_quote' AND REFERENCED_COLUMN_NAME = 'entity_id' AND TABLE_SCHEMA = '[your-magento-db-name]';

This is why this step was so important:

+--------------------------+-------------+
| TABLE_NAME               | COLUMN_NAME |
+--------------------------+-------------+
| sales_flat_quote_address | quote_id    |
| sales_flat_quote_item    | quote_id    |
| sales_flat_quote_payment | quote_id    |
| task_system              | quote_id    |
+--------------------------+-------------+

There might be extensions that also reference it. And in my client's case, there was! Furthermore, it was a cascade FK. If we had deleted that, it would have deleted all the records in their task_system table.

We updated the task_system.quote_id to DEFAULT NULL and then the FK to ON DELETE SET_NULL.

Now we're safe to do this:

2. Delete all carts/quotes over 60 days old:

Kudos to: Fiasco Labs

START TRANSACTION;
DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY) LIMIT 100;
COMMIT;

OR, try this if you want to keep quotes referenced by the sales_flat_order table:

Kudos to: Fiasco Labs and Styopchik

START TRANSACTION;
DELETE FROM sales_flat_quote WHERE entity_id NOT IN (SELECT quote_id FROM sales_flat_order) AND `updated_at` < DATE_SUB(Now(),INTERVAL 60 DAY) LIMIT 100;
COMMIT;

Note: The LIMIT 100 here as this could completely lock up your database. Do it in batches.

2. Don't Assume!

Do not assume all is well.

mysql> SELECT COUNT(*) FROM sales_flat_quote_item;
+----------+
| COUNT(*) |
+----------+
|       21 |
+----------+
1 row in set (0.03 sec)

So we have some orphaned data. You need to get rid of that by JOINING the sales_flat_quote_ tables to the sales_flat_quote table where quote_id IS NULL. That's out of scope for this answer. just Google "deleting mysql orphaned records". Make backups!