Clean table mailchimp_sync_ecommerce

[webloft]

We have measured that the query

`SELECT `main_table`.`mailchimp_store_id` FROM `mailchimp_sync_ecommerce` AS `main_table`
 LEFT JOIN `db`.`core_config_data` AS `core_config` ON value = mailchimp_store_id WHERE (value is null) GROUP BY `mailchimp_store_id`

triggered by backend actions causes a waiting time of 10min(!)

In fact, the table is very big:

mailchimp_sync_ecommerce

type	COUNT(type)
CUS	528322
ORD	570670
PCD	848
PRL	99
PRO	174869
SUB	2273528

There seems no routine which cleans up this table. (Do we need all that old data?)

Additional: Which data can be removed manually? We would like to remove data from last year, is this save?

[gonzaloebiz]

Hi @webloft

no, you can't remove register from this table. This table contains if the object is synched or not, and the status of the synchronization of each object

Best

[webloft]

@gonzaloebiz but the question is: do we really need this status?

Our understanding is that the information is somehow temporarly since it's required to set up a task to sync of course but has no other effect if this data is already processed. Yes, the status is somehow useful in the backend but not required for mailchimp servers on the remote side.

And this issue shouldn't be closed since the the query is used in the Adminhtml controller and generates a server timeout by opening the configuration page.

[gonzaloebiz]

Comment this line https://github.com/mailchimp/mc-magento2/blob/cfa0c05d8bba4a529be9c2f76d55b752b0349b65/view/adminhtml/web/js/configapikey.js#L62 But you can delete the registers on this table

[webloft]

OK, thanks.

Sorry, to ask again but

But you can delete the registers on this table

I think we could remove data that has multiple entries? for example product data that was synched yesterday but also today (so we don't need the older entry?)

Big tables are also an issue for Cron Jobs and resource usage of the database server.

[gonzaloebiz]

In this table you have only one entry by object (products, orders, etc) by mailchimp store, if you sync one product today and sync the same product tomorrow, only one register exists for this product. The table can grow if you change the mailchimp store (we don't delete the registers for the old store to avoid re sync all the objects if you decide to return to this old store). The query that you point, try to know if you have registers that can be deleted. Keep in mind, for the subscribers, in the mailchimp_store_id field, you have an audience id You only can delete registers in this table, for mailchimp stores and mailchimp audiences that you don't use anymore (you must look at the core_config_data if you use it)

Best