Magento – How to Drop Tables in Database

databaseMySQL

It is ok to drop tables that end in _cl and _tmp

Does anyone have a comprehensive table clean up list? For example, can I clean up all the sales_flat_quote tables that are more than 30 days old?

TRUNCATE sales_flat_quote; 
TRUNCATE sales_flat_quote_address; 
TRUNCATE sales_flat_quote_address_item; 
TRUNCATE sales_flat_quote_item; 
TRUNCATE sales_flat_quote_item_option;

EDIT: Sorry this was a two part Question, Drop Tables that end if _cl or _tmp and truncate the other tables

enter image description here

Best Answer

The log_ can be safely truncated, but if admins use the traffic data provided to them in the admin, it will be reset. I've never run into that being a problem though and honestly wish they didn't exist since they are actually even useless where a reverse proxy is used. :)

I would suggest against truncating the quote tables.

The cron table is auto cleaned by the cron process. If it's bloated, you have problematic cron scheduler settings.

_tmp tables are MEMORY tables used for partial indexes. They should not be removed, but could be safely truncated assuming no index was running and no admins were signed into the back end.

_cl tables are a vital part of the Mview pattern which EE uses in it's indexers. Removing these would break all the triggers and ruin the incremental / async index process. I believe they can be truncated without causing issue.

In a migration, clearing log tables is one thing, clearing data tables is an entirely different story. Why mess with the data needlessly? It's pretty much begging for just one more thing to be at risk of going wrong during the migration. If it's being done as part of an upgrade (as opposed to server migration) then do it till your hearts content... In a staging environment, and test the tar out of and preflight everything before the moment of truth hits.

Related Solutions

Magento – select row based on foreign key

If your model extends Mage_Core_Model_Abstract and your resource model is configured correctly you should be able to load the row like this:

$model = Mage::getModel('prefs/prefsemail')->load($id);

Then all your columns should be found in $model->getData()

EDIT
If you want to get data from an other table using a FK try this:

$collection = Mage::getModel('prefs/prefsemail')->getCollection()->addFieldToFilter('fk_name', $fkValue);
foreach ($collection as $item){
    //do something with $item
}

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

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.

Best Answer

Related Solutions

Magento – select row based on foreign key

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

Related Topic