Very slow query when sending mailings via CiviMail
Overview
We have come across an issue where the database connection was timing out due to slow, long-running queries when the Send Scheduled Mailings job was running. We became aware of this when we set a maximum execution time limit of 3 minutes for MySQL queries. Mailings for one of our clients were not being sent because the database connection was timing out.
As a workaround, we have increased the maximum execution time to 10 minutes and reduced the Mailer Batch Limit and Mailer Job Size to 1,000. However, we'd like to fix the underlying issue that the queries are running so slowly.
When sending a mailing CiviCRM runs a query of the form:
SELECT r.contact_id, r.email_id, r.phone_id
FROM civicrm_mailing_recipients r
INNER JOIN civicrm_contact c on
(c.id = r.contact_id
AND c.is_deleted = 0
AND c.is_deceased = 0
AND c.do_not_email = 0
AND c.is_opt_out = 0
)
INNER JOIN civicrm_email e ON (r.email_id = e.id AND e.on_hold = 0)
WHERE r.mailing_id = 3271
LIMIT 18000, 1000
There are no indices for is_deceased
, do_not_email
or is_opt_out
so this query is very slow. (There is an index for is_deleted
. There should be an index for is_deceased
but this appears to be missing from this site.)
We have tested adding these indices in a development environment to see what difference it would make and this increase the speed of the query by a factor of one thousand:
- 20,000 rows with no indices = 80 seconds
- 20,000 rows with indices = 0.085 seconds
Reproduction steps
This is likely to only affect sites with a large number of contacts and large number of rows in the civicrm_mailing_recipients
table (in this case 46,000 contacts and 9 million rows respectively).
- Set a maximum execution time for MySQL of 3 minutes (say)
- Create a mailing to a large group of contacts (>20,000)
- Schedule the mailing to send
- Send Scheduled Mailings job fails with error: "Finished execution of Send Scheduled Mailings with result: Failure, Error message: DB Error: unknown error"
Current behaviour
No indices , is_deceased
do_not_email
or is_opt_out
in civicrm_contact
so queries run very slowly.
Expected behaviour
Indices for the above fields so that the query runs quickly.
Environment information
- CiviCRM: 5.62
- PHP: 8.1_
- CMS: N/A
- Database: MySQL 5.7.43
Comments
There are other speed improvements in #4045 but these relate to the speed of the user interface rather than the speed of sending emails.
Perhaps this can also help to reduce our collective CO2 emissions?