See comment in
https://phabricator.wikimedia.org/T335296#9159155
Upstream ref https://lab.civicrm.org/dev/core/-/issues/4567
I created an upstream issue https://lab.civicrm.org/dev/core/-/issues/4567 - but I think in the short term we need to rationalise our civicrm_mailing data
We have 22978433 records in civicrm_mailing_job that pre-date 2020 ....
We could
consolidate all our thank you mailing records in civicrm_mailing to 1 & update all the civicrm_mailing_job records
consolidate all our thank you mailing job records in civicrm_mailing_job to be just one job linked to the 1 mailing
update all the civicrm_mailing_event_queue records to link to this single job_id
not create new job ids as we thank - keep re-using
Doing the above should get the queries sane and allow the mailing dashboard to load & allow us to start querying stuff
It is further worth thinking about what we want from the older bounce records etc
My feeling is it might be worth creating activities for bounces less than x years old( or perhaps all 1.3m of them), and the unsubscribes & we should probably delete the delivereds & opens after just a month or 2 (AFAIK we have never looked at them)
- We should probably NOT log the data for the activities to log_civicrm_activity for activity_type_id = 170 (Bounce) and have a retention plan to delete the data from 'details' after ? 3 months?
+------------------------------------------+-------------+
mailing_table number_rows
civicrm_mailing 7075
civicrm_mailing_job 50296940
civicrm_mailing_event_queue 50094686
civicrm_mailing_event_bounce 1305082
civicrm_mailing_event_confirm 0
civicrm_mailing_event_delivered 50070600
civicrm_mailing_event_forward 0
civicrm_mailing_event_opened 421457
civicrm_mailing_event_reply 402
civicrm_mailing_event_subscribe 0
civicrm_mailing_event_trackable_url_open 62816
civicrm_mailing_event_unsubscribe 9168