Martin

I also got this problem and by cronjob (every minute at initial, and then maybe every hours after it is cleaned up) I'm running
DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY) LIMIT 50000;
(community version)

But, are anyone find out where the bug is, and why this isn't being done automatically?
I have 2 shops both on the same version, and the other one runs perfectly 

• sonassi

  My suggestion would be to reduce the interval from 60 days to ~15 days and remove the LIMIT altogether (it defeats the purpose of the date condition).

  The table is populated with quotes. A 'quote' is a customers cart contents before they checkout – and even after they have checked-out (but none are necessary to keep).

  It isn't a bug per se, but an oversight from Magento as to the performance hit of millions of quotes – it doesn't tend to scale well (that applies to EE and CE alike).

  Quotes are created when someone populates their basket, gets an estimate for their shipping method, calculating shopping cart price rules etc. Lots of 3rd party extensions create quotes to provide functionality (shipping quotes etc.) but do not factor in the mess they leave behind.

  Its likely you have a rogue 3rd party extension continually creating quotes. One prime example of this was a shipping price estimate in the mini-cart that created a quote on each page load for each customer!

  • Martin

    The reason for the limit is so the query doesn't take ages to run.
    (It will be run by cronjob every hour)

    Thanks for the explanation of the quote tables, just what I needed.
    Ill try and hunt down and see which extension that make quotes.

    But I'm now down to a pretty reasonable number of rows in all the quotes tables (around 100.000 rows in each table)

    • sonassi

      @d37929867953592467ba36d8827d3341:disqus  running the task every hour is pointless – as the interval is set to DAYS not HOURS – so you should only run it once per day. You will be needlessly grinding your site to a halt executing a cron that is locking rows every hour.

      Instead, change the CRON to daily, change the INTERVAL to 15 days and remove the LIMIT.

      All that being said, 100k rows is really small – so you shouldn't have an issue at all. Your issue sounds more like a hosting problem – we provide both Magento hosting and consultancy.

  • Mats-Ola Ström

    That would be a likely scenario. I wrote a module estimating shipping on each page, and ended up creating a new quote for each request. The magic lies in using Mage::getSingleton('checkout/cart') instead of Mage::getModel('checkout/cart').

Aleksey Korzun

The enterprise_customer_sales_flat_quote table does not have updated_at column. You have to match entity_id to order table.

• http://www.joshuapack.com MrJoshuaPack

  DELETE FROM enterprise_customer_sales_flat_quote WHERE entity_id IN ( SELECT entity_id FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY))

http://www.joshuapack.com MrJoshuaPack

Also, to the point of large database and taking a long time, you can use LIMIT 100000 so that you can do the deletes in increments. 

JacquieK

I use community 1.9. I thought the updated_at date would be the date the item was added to the cart, but after adding it to my report, it appears to be the date the specific item was last updated by the admin. Is there a way to get the date the item was placed in the cart?

• http://batman-news.com Bruce Wayne

  The items related are set in the table sales_flat_quote_item and the FK to the table sales_flat_quote is the field quote_id.
  This table has a field updated_at that represent the last time the item was updated (whether added or modified, i.e. the quantity).
  Regards!

  By Benjamin L.