Extreme performance issues with large wp_oses_attachments and wp_oses_email_attachments

[dr-yd]

I just opened a new merge request. We had an issue today that caused Offload SES to bring down our database server for almost an hour. Upon investigation, it turned out to be this query:

UPDATE wp_oses_attachments attachments
LEFT JOIN wp_oses_email_attachments email_attachments ON email_attachments.attachment_id = attachments.id
SET attachments.gc = 1
WHERE email_attachments.attachment_id IS NULL;

There are about 42k rows in wp_oses_attachments and about 130k in wp_oses_email_attachments. The slowlog stated Rows_examined: 5719954827.

As the only purpose of this query is to find attachments that are not referenced in wp_oses_email_attachments any more, it can be rewritten as such:

UPDATE wp_oses_attachments attachments
LEFT JOIN (SELECT * FROM wp_oses_email_attachments GROUP BY attachment_id) email_attachments
ON email_attachments.attachment_id = attachments.id
SET attachments.gc = 1
WHERE email_attachments.attachment_id IS NULL

This reduced the runtime of the query to 700ms with no noticeable performance impact.

[dr-yd]

Link to merge request: #32