16

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?

1

1 Answer 1

17

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).

3
  • Is it safe to truncate these tables from time to time? Currently at 25m records. Commented Jan 20, 2015 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
    Commented Jan 21, 2015 at 13:41
  • 6
    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.
    Commented Feb 4, 2015 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.