Answers.Body: <p>I created an extension <a href="https://civicrm.org/extensions/archive-mailing" rel="nofollow noreferrer">archive mailing</a> where a Scheduled Job can be enabled to automatically archive mailings (and compile statistics, cleanup the mailing tables) after a specified period.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-05-04T14:32:31.993Z
Answers.LastActivityDate: 2022-05-04T14:32:31.993Z
Answers.UserDisplayName: bgm
Answers.Body: <p>If I'm understanding correctly, I'd say there are two possible solutions here.  The first is the easier one where you don't care about keeping the stats or history of the mailings in Civi.  If that's case, then you can probably use the API explorer to delete old mailings, which would be equivalent to clicking to delete them from the Scheduled and Sent Mailings management list.  This doesn't really archive them per se.  You're just deleting them, but doing so through the explorer seems cleaner than deleting through the MySQL cli.</p>
  
  <p>The second option is what my clients want which is to keep the stats and remove the bloat, but this currently doesn't exist as far as I know.  The largest tables they have are <code>civicrm_mailing_recipients</code>, <code>civicrm_mailing_event_queue</code> and <code>civicrm_mailing_event_delivered</code>.  (The activity tables also get pretty big, but that's another issue.)   I've schemed a solution that would require transferring the stats data to another table(s) to be able to reference them at a later date (not all that difficult), but hooking those new table(s) into the search functions, reports and other areas where it's needed is a taller order.  I haven't really thought through this in any detail, so I'm sure there's lots I haven't considered.  Maybe there's an interim solution between these two.  Clicking Archive removes data from the some of the processing tables but keeps the stats tables in place?</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-14T20:15:53.293Z
Answers.Text: I guess ideally clicking Archive would indeed compile the stats and keep them in a separate table and cleanup all of the queue data, but I agree that it would be complicated if we want search/reports to continue working. I suspect most people would not need that; I would create a job that auto-archives after a certain number of months or years, and worst case, provide an alternate report for archived mailings.
Answers.UserDisplayName: bgm
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-14T18:57:48.250Z
Answers.LastActivityDate: 2019-02-14T20:19:48.013Z
Answers.LastEditDate: 2019-02-14T20:19:48.013Z
Answers.UserDisplayName: Joseph Lacey
Body: <p>On CiviCRM installations that use CiviMail extensively, the mailing tables can become pretty big. For example, I'm looking at a database with a <code>civicrm_mailing_recipients</code> table that has 4 million entries since 2011.</p>
  
  <p>Assuming that the stats from old mailings are not relevant anymore, what would be the best way to archive the data?</p>
  
  <p>Ex: we are in 2019, stats from before 2016 could be archived elsewhere.</p>
  
  <p>I did a bit of digging and found this <a href="https://forum.civicrm.org/index.php%3Ftopic=28085.0.html" rel="nofollow noreferrer">old forum post</a> which sounds like a good start. There was also <a href="https://github.com/civicrm/civicrm-core/pull/7728" rel="nofollow noreferrer">this PR</a> back in 2016 that helped avoid breakage of click-through links when mailing data was deleted.</p>
  
  <p>Related SE question: <a href="https://civicrm.stackexchange.com/questions/7182/does-archiving-mailings-do-anything-in-particular">Does archiving mailings do anything in particular</a>.</p>
  
Tags: <civimail>
Title: Best way to cleanup CiviMail database tables?
ThreadText: # Best way to cleanup CiviMail database tables?
  
  (Asked by: username_0 on 2019-02-14)
  
  On CiviCRM installations that use CiviMail extensively, the mailing tables can become pretty big. For example, I'm looking at a database with a `civicrm_mailing_recipients` table that has 4 million entries since 2011.
  
  Assuming that the stats from old mailings are not relevant anymore, what would be the best way to archive the data?
  
  Ex: we are in 2019, stats from before 2016 could be archived elsewhere.
  
  I did a bit of digging and found this [old forum post](https://forum.civicrm.org/index.php%3Ftopic=28085.0.html) which sounds like a good start. There was also [this PR](https://github.com/civicrm/civicrm-core/pull/7728) back in 2016 that helped avoid breakage of click-through links when mailing data was deleted.
  
  Related SE question: [Does archiving mailings do anything in particular](https://civicrm.stackexchange.com/questions/7182/does-archiving-mailings-do-anything-in-particular).
  
  **Tags:** civimail
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2019-02-14. Score: 3 | Accepted answer
  
  If I'm understanding correctly, I'd say there are two possible solutions here. The first is the easier one where you don't care about keeping the stats or history of the mailings in Civi. If that's case, then you can probably use the API explorer to delete old mailings, which would be equivalent to clicking to delete them from the Scheduled and Sent Mailings management list. This doesn't really archive them per se. You're just deleting them, but doing so through the explorer seems cleaner than deleting through the MySQL cli.
  
  The second option is what my clients want which is to keep the stats and remove the bloat, but this currently doesn't exist as far as I know. The largest tables they have are `civicrm_mailing_recipients`, `civicrm_mailing_event_queue` and `civicrm_mailing_event_delivered`. (The activity tables also get pretty big, but that's another issue.) I've schemed a solution that would require transferring the stats data to another table(s) to be able to reference them at a later date (not all that difficult), but hooking those new table(s) into the search functions, reports and other areas where it's needed is a taller order. I haven't really thought through this in any detail, so I'm sure there's lots I haven't considered. Maybe there's an interim solution between these two. Clicking Archive removes data from the some of the processing tables but keeps the stats tables in place?
  
  > **Comments:**
  > **username_0**: I guess ideally clicking Archive would indeed compile the stats and keep them in a separate table and cleanup all of the queue data, but I agree that it would be complicated if we want search/reports to continue working. I suspect most people would not need that; I would create a job that auto-archives after a certain number of months or years, and worst case, provide an alternate report for archived mailings.  
  
  ---
  
  ### Answer by username_0 on 2022-05-04. Score: 4
  
  I created an extension [archive mailing](https://civicrm.org/extensions/archive-mailing) where a Scheduled Job can be enabled to automatically archive mailings (and compile statistics, cleanup the mailing tables) after a specified period.
  
  
  ---
  
  