Answers.Body: <p><code>civicrm_mailing_recipients</code> is used to match mailings to the intended recipients.  If you send three mailings to the same group of 5,000 contacts, you'll generate 15,000 records in this table.  It's necessary for generating mailing reports.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-06T04:51:12.040Z
Answers.LastActivityDate: 2017-01-06T04:51:12.040Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.Body: <p><code>civicrm_mailing_event_queue</code> keeps track of which email addresses a mailing is being sent to (or which phone number a mass SMS is being sent to): stores email id or phone id, contact id, mailing job id and a hash used to validate unsubscribes.</p>
  
  <p>It links to other tables to record various types of events, including: civicrm_mailing_event_bounce to record bounces, civicrm_mailing_event_delivered to record delivery, civicrm_mailing_event_opened, civicrm_mailing_event_reply, civicrm_mailing_event_trackable_url_open, civicrm_mailing_event_subscribe, civicrm_mailing_event_unsubscribe to record the corresponding events.</p>
  
  <p>Like <code>civicrm_mailing_recipients</code> (described in Jon G's answer) it is used for mailing reports.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-08T14:32:04.343Z
Answers.LastActivityDate: 2017-08-08T14:32:04.343Z
Answers.UserDisplayName: davejenx
Body: <p>Tomorrow, I will again begin the process of updating CiviCRM. I know that as the upgrade process is going through 4.7.8, it upgrades mailing foreign key constraints in <code>civicrm_mailing_event_queue</code> and <code>civicrm_mailing_recipients</code> tables, which have 2,230,662 and 3,025,260 rows, respectively. This causes this portion of the upgrade to be extremely long, and I had thought it stalled. </p>
  
  <ol>
  <li>What exactly are these tables for?</li>
  <li>How can I trim these tables down a bit? (i.e. is it as simple as deleting old emails that were sent?)</li>
  <li>What are the ramifications to our constituent records of trimming it?</li>
  </ol>
  
  <p><code>Describe civicrm_mailing_event_queue</code> in mysql:</p>
  
  <pre><code>+------------+------------------+------+-----+---------+----------------+
  | Field      | Type             | Null | Key | Default | Extra          |
  +------------+------------------+------+-----+---------+----------------+
  | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | job_id     | int(10) unsigned | NO   | MUL | NULL    |                |
  | email_id   | int(10) unsigned | YES  | MUL | NULL    |                |
  | contact_id | int(10) unsigned | NO   | MUL | NULL    |                |
  | hash       | varchar(255)     | NO   |     | NULL    |                |
  | phone_id   | int(10) unsigned | YES  | MUL | NULL    |                |
  +------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Here's an example of what one of the rows from above is storing:</p>
  
  <pre><code>mysql&gt; SELECT * FROM civicrm_mailing_recipients LIMIT 10;
  +-----+------------+------------+----------+----------+
  | id  | mailing_id | contact_id | email_id | phone_id |
  +-----+------------+------------+----------+----------+
  | 560 |          7 |          3 |        3 |     NULL |
  | 561 |          7 |      14561 |    14561 |     NULL |
  | 562 |          7 |      31443 |    31443 |     NULL |
  | 563 |          7 |      31481 |    31482 |     NULL |
  | 565 |          8 |      14561 |    14561 |     NULL |
  | 566 |          8 |      31534 |    31521 |     NULL |
  | 569 |         10 |      14561 |    14561 |     NULL |
  | 570 |         10 |      39403 |    37916 |     NULL |
  | 572 |         11 |      14561 |    14561 |     NULL |
  | 573 |         11 |      39403 |    37916 |     NULL |
  +-----+------------+------------+----------+----------+
  </code></pre>
  
  <p><code>Describe civicrm_mailing_recipients</code> in mysql:</p>
  
  <pre><code>+------------+------------------+------+-----+---------+----------------+
  | Field      | Type             | Null | Key | Default | Extra          |
  +------------+------------------+------+-----+---------+----------------+
  | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | mailing_id | int(10) unsigned | NO   | MUL | NULL    |                |
  | contact_id | int(10) unsigned | NO   | MUL | NULL    |                |
  | email_id   | int(10) unsigned | YES  | MUL | NULL    |                |
  | phone_id   | int(10) unsigned | YES  | MUL | NULL    |                |
  +------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Here's an example of what one of the rows from above is storing:</p>
  
  <pre><code>   mysql&gt; SELECT * FROM civicrm_mailing_event_queue LIMIT 10;
  +----+--------+----------+------------+------------------+----------+
  | id | job_id | email_id | contact_id | hash             | phone_id |
  +----+--------+----------+------------+------------------+----------+
  |  8 |      9 |        3 |          3 | fda886fa89xxxxxx |     NULL |
  |  9 |      9 |    14561 |      14561 | da71a735ffxxxxxx |     NULL |
  | 10 |      9 |    31443 |      31443 | 3256b6d93axxxxxx |     NULL |
  | 11 |      9 |    31482 |      31481 | 09c8d6d6ddxxxxxx |     NULL |
  | 13 |     11 |    14561 |      14561 | 00e12468baxxxxxx |     NULL |
  | 14 |     11 |    31521 |      31534 | 767409eb3fxxxxxx |     NULL |
  | 15 |     14 |    14561 |      14561 | f5a36df7a3xxxxxx |     NULL |
  | 16 |     14 |    37916 |      39403 | b6fee3b110xxxxxx |     NULL |
  | 17 |     16 |    14561 |      14561 | f139097393xxxxxx |     NULL |
  | 18 |     16 |    37916 |      39403 | 7a242aa0e5xxxxxx |     NULL |
  +----+--------+----------+------------+------------------+----------+
  </code></pre>
  
  <p>Thanks in advance!</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-12-27T09:36:57.923Z
Comments.Text: This question is semi-related to but follows this question: http://civicrm.stackexchange.com/questions/16369/upgrade-froze-at-4-7-8-cant-find-valid-value-for-qrid
Comments.UserDisplayName: Christia
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-06T00:34:20.147Z
Comments.Text: This question answers my question: http://civicrm.stackexchange.com/questions/16432/how-to-reduce-database-bloat-for-faster-upgrading/16448#16448
Comments.UserDisplayName: Christia
Tags: <civimail><drupal><upgrade><mysql>
Title: What are these big MySQL mailing tables for?
ThreadText: # What are these big MySQL mailing tables for?
  
  (Asked by: username_0 on 2016-12-27)
  
  Tomorrow, I will again begin the process of updating CiviCRM. I know that as the upgrade process is going through 4.7.8, it upgrades mailing foreign key constraints in `civicrm_mailing_event_queue` and `civicrm_mailing_recipients` tables, which have 2,230,662 and 3,025,260 rows, respectively. This causes this portion of the upgrade to be extremely long, and I had thought it stalled.
  
  1. What exactly are these tables for?
  2. How can I trim these tables down a bit? (i.e. is it as simple as deleting old emails that were sent?)
  3. What are the ramifications to our constituent records of trimming it?
  
  `Describe civicrm_mailing_event_queue` in mysql:
  
  ```
  +------------+------------------+------+-----+---------+----------------+
  | Field      | Type             | Null | Key | Default | Extra          |
  +------------+------------------+------+-----+---------+----------------+
  | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | job_id     | int(10) unsigned | NO   | MUL | NULL    |                |
  | email_id   | int(10) unsigned | YES  | MUL | NULL    |                |
  | contact_id | int(10) unsigned | NO   | MUL | NULL    |                |
  | hash       | varchar(255)     | NO   |     | NULL    |                |
  | phone_id   | int(10) unsigned | YES  | MUL | NULL    |                |
  +------------+------------------+------+-----+---------+----------------+
  
  ```
  
  Here's an example of what one of the rows from above is storing:
  
  ```
  mysql> SELECT * FROM civicrm_mailing_recipients LIMIT 10;
  +-----+------------+------------+----------+----------+
  | id  | mailing_id | contact_id | email_id | phone_id |
  +-----+------------+------------+----------+----------+
  | 560 |          7 |          3 |        3 |     NULL |
  | 561 |          7 |      14561 |    14561 |     NULL |
  | 562 |          7 |      31443 |    31443 |     NULL |
  | 563 |          7 |      31481 |    31482 |     NULL |
  | 565 |          8 |      14561 |    14561 |     NULL |
  | 566 |          8 |      31534 |    31521 |     NULL |
  | 569 |         10 |      14561 |    14561 |     NULL |
  | 570 |         10 |      39403 |    37916 |     NULL |
  | 572 |         11 |      14561 |    14561 |     NULL |
  | 573 |         11 |      39403 |    37916 |     NULL |
  +-----+------------+------------+----------+----------+
  
  ```
  
  `Describe civicrm_mailing_recipients` in mysql:
  
  ```
  +------------+------------------+------+-----+---------+----------------+
  | Field      | Type             | Null | Key | Default | Extra          |
  +------------+------------------+------+-----+---------+----------------+
  | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | mailing_id | int(10) unsigned | NO   | MUL | NULL    |                |
  | contact_id | int(10) unsigned | NO   | MUL | NULL    |                |
  | email_id   | int(10) unsigned | YES  | MUL | NULL    |                |
  | phone_id   | int(10) unsigned | YES  | MUL | NULL    |                |
  +------------+------------------+------+-----+---------+----------------+
  
  ```
  
  Here's an example of what one of the rows from above is storing:
  
  ```
     mysql> SELECT * FROM civicrm_mailing_event_queue LIMIT 10;
  +----+--------+----------+------------+------------------+----------+
  | id | job_id | email_id | contact_id | hash             | phone_id |
  +----+--------+----------+------------+------------------+----------+
  |  8 |      9 |        3 |          3 | fda886fa89xxxxxx |     NULL |
  |  9 |      9 |    14561 |      14561 | da71a735ffxxxxxx |     NULL |
  | 10 |      9 |    31443 |      31443 | 3256b6d93axxxxxx |     NULL |
  | 11 |      9 |    31482 |      31481 | 09c8d6d6ddxxxxxx |     NULL |
  | 13 |     11 |    14561 |      14561 | 00e12468baxxxxxx |     NULL |
  | 14 |     11 |    31521 |      31534 | 767409eb3fxxxxxx |     NULL |
  | 15 |     14 |    14561 |      14561 | f5a36df7a3xxxxxx |     NULL |
  | 16 |     14 |    37916 |      39403 | b6fee3b110xxxxxx |     NULL |
  | 17 |     16 |    14561 |      14561 | f139097393xxxxxx |     NULL |
  | 18 |     16 |    37916 |      39403 | 7a242aa0e5xxxxxx |     NULL |
  +----+--------+----------+------------+------------------+----------+
  
  ```
  
  Thanks in advance!
  
  **Tags:** civimail, drupal, upgrade, mysql
  
  > **Comments:**
  > **username_0**: This question is semi-related to but follows this question: http://civicrm.stackexchange.com/questions/16369/upgrade-froze-at-4-7-8-cant-find-valid-value-for-qrid  
  > **username_0**: This question answers my question: http://civicrm.stackexchange.com/questions/16432/how-to-reduce-database-bloat-for-faster-upgrading/16448#16448  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2017-01-06. Score: 1 | Accepted answer
  
  `civicrm_mailing_recipients` is used to match mailings to the intended recipients. If you send three mailings to the same group of 5,000 contacts, you'll generate 15,000 records in this table. It's necessary for generating mailing reports.
  
  
  ---
  
  ### Answer by username_2 on 2017-08-08. Score: 1
  
  `civicrm_mailing_event_queue` keeps track of which email addresses a mailing is being sent to (or which phone number a mass SMS is being sent to): stores email id or phone id, contact id, mailing job id and a hash used to validate unsubscribes.
  
  It links to other tables to record various types of events, including: civicrm\_mailing\_event\_bounce to record bounces, civicrm\_mailing\_event\_delivered to record delivery, civicrm\_mailing\_event\_opened, civicrm\_mailing\_event\_reply, civicrm\_mailing\_event\_trackable\_url\_open, civicrm\_mailing\_event\_subscribe, civicrm\_mailing\_event\_unsubscribe to record the corresponding events.
  
  Like `civicrm_mailing_recipients` (described in Jon G's answer) it is used for mailing reports.
  
  
  ---
  
  