See comment in
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 ....
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?