Newsletter SQL query optimization 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?


DB Design #1
DB Design #2
Use EXPLAIN in the sql console to determine what the query optimizer is doing with each query.  This will give you clues as to how each query performs, and may also help you optimize further, for example by indicating places where adding an index will speed the query up. 
Run each of the queries in a loop and benchmark how long they take! If you're using PHP just wrap the query execution in a for loop and execute it 100 times or so.  Record the microtime() before you start and do it again at the end.  Subtract the start time from the end time and you'll have your benchmark.