What are these big MySQL mailing tables for?

Question

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!

Answer 1

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 2

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.