15

I just spotted the mentioned tables in the database. I think they are new in Magento EE 1.13 and might be related to the new indexing.

+----------------------------------------+
| catalog_category_flat_cl               |
| catalog_category_product_cat_cl        |
| catalog_category_product_index_cl      |
| catalog_product_flat_cl                |
| catalog_product_index_price_cl         |
| cataloginventory_stock_status_cl       |
| catalogsearch_fulltext_cl              |
| enterprise_url_rewrite_category_cl     |
| enterprise_url_rewrite_product_cl      |
| enterprise_url_rewrite_redirect_cl     |
+----------------------------------------+

How do those tables function? What is the purpose?

Are they automatically cleaned after some time?

Does it make sense to include those tables into backups?

15

These changelog (hence the _cl suffix) tables are filled via MySQL triggers whenever a specific entity is changed.
Then the indexer cron job (running every minute) will apply these changelogs as incremental updates to the Magento indexes.

The benefit of using MySQL triggers to fill the changelog tables is that it works even if new data is added using plain SQL, without involving PHP.
This eliminates the need to run a full reindex if you are using a non-standard import method (or even the Mage_ImportExport module).

  • Is it safe to truncate these tables from time to time? Currently at 25m records. – Steve Robbins Jan 20 '15 at 22:33
  • Not sure. The problem is that Magento might depend on the version stored in that table. I would expext it is safe to delete all except the latest version, but do so on your risk. Maybe even truncating is safe - I don't know. – Vinai Jan 21 '15 at 13:41
  • 5
    The Enterprise_Mview module already has the ability to clean these tables. It takes the latest version_id for each table from enterprise_mview_metadata and deletes any rows with a version_id lower than that. You can enable index cleaning by going to System > Configuration > (ADVANCED section) > Index Management and setting Enable Scheduled Cleanup to Yes under Index Clean Schedule. – Tyler V. Feb 4 '15 at 20:47

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.