16

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.
Improve this question

4 Answers 4

Reset to default
26 
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.

Improve this answer
4
  • I see that the size of the other table decreases, so the cascading works. I'm going to test the AOE module. That's a good tip!
    – SPRBRN
    Sep 23, 2014 at 7:34
  • 1
    I ran that script many times, with limit 1000000, until all records older than 60 days were gone. Then I installed the AOE Quote Cleaner module. I didn't use the AOE module for the 20m deletes, as the website stopped working - on the test machine at least. The script didn't pose any problems.
    – SPRBRN
    Sep 23, 2014 at 10:59
  • 1
    You're welcome! I had to tackle the problem way back before Fabrizio's module became available and so went the observer route after doing something similar to your cleanup. It took about 20 passes to clear the gunk. Now that it's on scheduled cleanup, no worries!
    – Fiasco Labs
    Sep 23, 2014 at 14:35
  • I want to delete some records which customer doesn't exist.
    – Lovely Setia
    Sep 4, 2018 at 12:17
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.

Improve this answer
3
  • Why would I want to create my own module when AOE has a working module ready for download?
    – SPRBRN
    Sep 23, 2014 at 15:00
  • 2
    The AOE extensions are great, so I'm not discrediting those at all. But to give you reasons.. 1. Avoid using manual queries in extensions. 2. Try to make you extensions independent, if possible. DELETE queries like these depend on the database constraint, which may be altered in the future. 3. Use Magento factory methods appropriately.
    – laketuna
    Sep 23, 2014 at 15:15
  • Seems like that one was made a while ago @musicliftsme.. usually they're pretty good
    – Erfan
    Jan 4, 2016 at 8:40
2 
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.

Improve this answer
0

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
Improve this answer
1
  • 1
    Kind of, mixing with @fiasco's answer: DELETE FROM quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY)
    – nicolallias
    May 28, 2018 at 13:44

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.