MySQL Maintenance – How to Handle Huge sales_flat_quote Tables and Remove Abandoned Cart Data

logmaintenanceMySQL

Situation

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

sales_flat_quote_shipping_rate: 3045MB
sales_flat_quote_address: 1688MB
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.

Best Answer

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.

Related Solutions

Magento – How to deal with 61 tables join limit when building magento collection

There's no circumventing, however, you don't always need a join. If custom tables are involved, denormalize them. Serialize some data into JSON/PHP serialized objects.

You can also use UNION to merge multiple SELECT statements. You can use subqueries, and you can use multiple, sequential, queries to append to a custom collection.

In short, rethink your design or your implementation.

Other thoughts:

Or look into Magento's compatibility with other RDBMS' such as SQL Server or Oracle. Look into the specs of MariaDB, the fork of MySQL to see if it's compatible. See if there's some community support (not sure off the top of my head) for Postgres in Magento.

SQL Join – How to Get Distinct Entries from Joined Tables

What you could consider doing is adding a group by clause for the visitor_id column.

$collection = Mage::getModel('log/visitor')->getCollection()->addFieldToFilter('store_id', array('eq' => 1));
$collection->getSelect()
    ->join(
        array('log_customer'=> $collection->getTable('log/customer')),
        'main_table.`visitor_id`= log_customer.`visitor_id`'
    )->group('main_table.visitor_id');

But you might also need to add an order by to make sure that you get the newest results first.

Best Answer

Related Solutions

Magento – How to deal with 61 tables join limit when building magento collection

SQL Join – How to Get Distinct Entries from Joined Tables

Related Topic