Have a question about CiviCRM? Get it answered quickly at the new CiviCRM Stack Exchange Q+A siteThis forum was archived on 25 November 2017. Learn more.How to get involved.What to do if you think you've found a bug.
use <database_name>;create temporary table temp_mail_job_ids select id as job_id, mailing_idfrom civicrm_mailing_jobwhere start_date < date '2009-01-01' ;create index temp_job_id on temp_mail_job_ids ( job_id ) ;create index temp_mailing_id on temp_mail_job_ids ( mailing_id ) ;delete from civicrm_mailing_trackable_url where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;delete from civicrm_mailing_group where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;delete from civicrm_mailing_event_unsubscribewhere time_stamp < date '2009-01-01' ;delete from civicrm_mailing_event_trackable_url_openwhere time_stamp < date '2009-01-01' ;delete from civicrm_mailing_event_replywhere time_stamp < date '2009-01-01' ;delete from civicrm_mailing_event_queuewhere job_id in ( select job_id from temp_mail_job_ids ) ;delete from civicrm_mailing_event_openedwhere time_stamp < date '2009-01-01' ;delete from civicrm_mailing_event_deliveredwhere time_stamp < date '2009-01-01' ;delete from civicrm_mailing_event_bouncewhere time_stamp < date '2009-01-01' ;delete from civicrm_mailingwhere id in ( select mailing_id from temp_mail_job_ids ) ;delete from civicrm_activity_history where activity_type = 'Email sent' and activity_id in ( select mailing_id from temp_mail_job_ids ) ;delete from civicrm_mailing_jobwhere id in ( select job_id from temp_mail_job_ids ) ;optimize table civicrm_activity_history ;optimize table civicrm_mailing_event_delivered ;optimize table civicrm_mailing_event_bounce ;optimize table civicrm_mailing ;optimize table civicrm_mailing_event_opened ;optimize table civicrm_mailing_event_queue ;optimize table civicrm_mailing_event_reply ;optimize table civicrm_mailing_event_trackable_url_open ;optimize table civicrm_mailing_job ;optimize table civicrm_mailing_trackable_url ;optimize table civicrm_mailing_group ;optimize table civicrm_mailing_event_unsubscribe ;
use <database_name> ;create table z_archive_activity_history select * from civicrm_activity_history limit 5 ;create table z_archive_email_history select * from civicrm_email_history limit 5 ;create table z_archive_mailing select * from civicrm_mailing limit 5 ;create table z_archive_mailing_event_bounce select * from civicrm_mailing_event_bounce limit 5 ;create table z_archive_mailing_event_delivered select * from civicrm_mailing_event_delivered limit 5 ;create table z_archive_mailing_event_opened select * from civicrm_mailing_event_opened limit 5 ;create table z_archive_mailing_event_queue select * from civicrm_mailing_event_queue limit 5 ;create table z_archive_mailing_event_reply select * from civicrm_mailing_event_reply limit 5 ;create table z_archive_mailing_event_trackable_url_open select * from civicrm_mailing_event_trackable_url_open limit 5 ;create table z_archive_mailing_event_unsubscribe select * from civicrm_mailing_event_unsubscribe limit 5 ;create table z_archive_mailing_group select * from civicrm_mailing_group limit 5 ;create table z_archive_mailing_job select * from civicrm_mailing_job limit 5 ;create table z_archive_mailing_trackable_url select * from civicrm_mailing_trackable_url limit 5 ;truncate table z_archive_activity_history ;truncate table z_archive_email_history ;truncate table z_archive_mailing ;truncate table z_archive_mailing_event_bounce ;truncate table z_archive_mailing_event_delivered ;truncate table z_archive_mailing_event_opened ;truncate table z_archive_mailing_event_queue ;truncate table z_archive_mailing_event_reply ;truncate table z_archive_mailing_event_trackable_url_open ;truncate table z_archive_mailing_event_unsubscribe ;truncate table z_archive_mailing_group ;truncate table z_archive_mailing_job ;truncate table z_archive_mailing_trackable_url ;alter table z_archive_activity_history engine = archive ;alter table z_archive_email_history engine = archive ;alter table z_archive_mailing engine = archive ;alter table z_archive_mailing_event_bounce engine = archive ;alter table z_archive_mailing_event_delivered engine = archive ;alter table z_archive_mailing_event_opened engine = archive ;alter table z_archive_mailing_event_queue engine = archive ;alter table z_archive_mailing_event_reply engine = archive ;alter table z_archive_mailing_event_trackable_url_open engine = archive ;alter table z_archive_mailing_event_unsubscribe engine = archive ;alter table z_archive_mailing_group engine = archive ;alter table z_archive_mailing_job engine = archive ;alter table z_archive_mailing_trackable_url engine = archive ;
use <database_name> ;create temporary table temp_mail_job_ids select id as job_id, mailing_idfrom civicrm_mailing_jobwhere start_date < date '2009-01-01' ;create index temp_job_id on temp_mail_job_ids ( job_id ) ;create index temp_mailing_id on temp_mail_job_ids ( mailing_id ) ;start transaction ;insert into z_archive_activity_history select * from civicrm_activity_history where activity_type = 'Email sent' and activity_id in ( select mailing_id from temp_mail_job_ids ) ;delete from civicrm_activity_history where activity_type = 'Email sent' and activity_id in ( select mailing_id from temp_mail_job_ids ) ;commit ;start transaction ;insert into z_archive_email_history select * from civicrm_email_history where sent_date < date '2009-01-01' ;delete from civicrm_email_history where sent_date < date '2009-01-01' ;commit ;start transaction ;insert into z_archive_mailing select * from civicrm_mailing where id in ( select mailing_id from temp_mail_job_ids ) ; delete from civicrm_mailing where id in ( select mailing_id from temp_mail_job_ids ) ;commit ;start transaction ;insert into z_archive_mailing_event_bounce select * from civicrm_mailing_event_bounce where time_stamp < date '2009-01-01' ; delete from civicrm_mailing_event_bouncewhere time_stamp < date '2009-01-01' ;commit ;start transaction ;insert into z_archive_mailing_event_delivered select * from civicrm_mailing_event_delivered where time_stamp < date '2009-01-01' ; delete from civicrm_mailing_event_delivered where time_stamp < date '2009-01-01' ;commit ;start transaction ;insert into z_archive_mailing_event_opened select * from civicrm_mailing_event_opened where time_stamp < date '2009-01-01' ;delete from civicrm_mailing_event_opened where time_stamp < date '2009-01-01' ;commit ;start transaction ;insert into z_archive_mailing_event_queue select * from civicrm_mailing_event_queue where job_id in ( select job_id from temp_mail_job_ids ) ; delete from civicrm_mailing_event_queue where job_id in ( select job_id from temp_mail_job_ids ) ;commit ;start transaction ;insert into z_archive_mailing_event_reply select * from civicrm_mailing_event_reply where time_stamp < date '2009-01-01' ; delete from civicrm_mailing_event_reply where time_stamp < date '2009-01-01' ;commit ;start transaction ;insert into z_archive_mailing_event_trackable_url_open select * from civicrm_mailing_event_trackable_url_open where time_stamp < date '2009-01-01' ; delete from civicrm_mailing_event_trackable_url_open where time_stamp < date '2009-01-01' ;commit ;start transaction ;insert into z_archive_mailing_event_unsubscribe select * from civicrm_mailing_event_unsubscribe where time_stamp < date '2009-01-01' ;delete from civicrm_mailing_event_unsubscribewhere time_stamp < date '2009-01-01' ;commit ;start transaction ;insert into z_archive_mailing_group select * from civicrm_mailing_group where mailing_id in ( select mailing_id from temp_mail_job_ids ) ; delete from civicrm_mailing_group where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;commit ;start transaction ;insert into z_archive_mailing_trackable_url select * from civicrm_mailing_trackable_url where mailing_id in ( select mailing_id from temp_mail_job_ids ) ; delete from civicrm_mailing_trackable_url where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;commit ;; z_archive_mailing_job must come after most other tables; cuz it is the source of the temp table IDsstart transaction ;insert into z_archive_mailing_job select * from civicrm_mailing_job where id in ( select job_id from temp_mail_job_ids ) ; delete from civicrm_mailing_job where id in ( select job_id from temp_mail_job_ids ) ;commit ;optimize table civicrm_activity_history ;optimize table civicrm_email_history ;optimize table civicrm_mailing ;optimize table civicrm_mailing_event_bounce ;optimize table civicrm_mailing_event_delivered ;optimize table civicrm_mailing_event_opened ;optimize table civicrm_mailing_event_queue ;optimize table civicrm_mailing_event_reply ;optimize table civicrm_mailing_event_trackable_url_open ;optimize table civicrm_mailing_event_unsubscribe ;optimize table civicrm_mailing_group ;optimize table civicrm_mailing_job ;optimize table civicrm_mailing_trackable_url ;