4

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

  • You have multiple sales_flat_quote tables? – philwinkle Oct 23 '13 at 17:32
  • Phil, I added the tables I thought I could truncate – brentwpeterson Oct 23 '13 at 19:11
  • sales_flat_order references quote_id so I personally would not discard quotes that are referenced (not a constraint) from elsewhere. – Kristof at Fooman Oct 23 '13 at 19:36
  • Phil, sorry I updated my question again :( – brentwpeterson Oct 23 '13 at 19:57
6

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.

2

You can truncate following tables also:
-cron_schedule
-index_event
-importexport_importdata
-log_url
-log_url_info
-log_visitor
-log_visitor_info
-log_quote
-log_customer
-sales_flat_quote_*
-sales_flat_quote_shipping_rate

And you can also truncate these tables before db migration if db size is very huge, but after migration you should re-index the store:
-core_url_rewrite
-catalog_category_flat_store_*
-catalog_product_flat_*
-catalog_product_index_price
-catalog_product_index_price_idx
-catalogsearch_fulltext

Particularly I truncate above tables before db migration and size of dump will be decreased to about 40%, but in my opinion it is no efficiency to truncate or delete that tables which you give in question.

2

Be careful truncating your change logs (tables ending in _cl). This will reset the auto increment ID and will fall out of sync with the values in the enterprise_mview_metadata table. In theory you would need to reset the version ID on the entries for the corresponding tables which were truncated.

If this does get out of sync you could end up with indexing (or lack of indexing) issues.

1

Just a bit of terminology to clear up:

  • DROP in SQL means to forceably delete the table.

  • TRUNCATE means to keep the table, but empty it of data, and reset the Auto Increment to 1.

However, rather than manually cleaning quotes you should install the following plugin which cleans quotes on a schedule:

https://github.com/fbrnc/Aoe_QuoteCleaner

The schedule is configurable from within System > Configuration > System

  • Phil, I clarified my question: Drop some tables, Truncate others – brentwpeterson Oct 23 '13 at 19:57
  • Yet another AoE module I need to dig into... ;) – davidalger Oct 24 '13 at 2:01

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.