I want to build a small newsletter like tool which sends mails in pre-defined timespans. First after registration, second 14 days later, third 7 days later etc.
I came across for two database designs and those two queries:
Create a separate table (dispatch) and put the next mails into it (can be done by cronjob):
SELECT m.campaign_id, s.name, s.email, m.subject, m.body FROM sp_dispatch AS d INNER JOIN sp_subscribers AS s ON s.id = d.recipient_id INNER JOIN sp_mailings AS m ON m.id = d.mailing_id WHERE d.time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 31 DAY) LIMIT 0, 500
Without a separate table and search through all mails when sending.
SELECT r.campaignID, DATE_ADD(r.startDate, INTERVAL t.timeOffset DAY) AS sendDate, r.name, r.email, t.subject, t.textMail FROM cw_recipients AS r INNER JOIN cw_triggerMails AS t ON r.campaignID = t.campaignID WHERE DATE_ADD(r.startDate, INTERVAL t.timeOffset DAY) BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 31 DAY) LIMIT 0, 500
What do you think is the better/faster/more reliable solution?