2
\$\begingroup\$

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:

  1. 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
    
  2. 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
\$\endgroup\$
  • 3
    \$\begingroup\$ Use explain query to see what it is using how many keys are involved. You can analyze it and show us the output. \$\endgroup\$ – YumYumYum Dec 30 '11 at 12:12
  • \$\begingroup\$ You should write description about the tables (include the cardinality between them too). \$\endgroup\$ – palacsint Feb 4 '12 at 12:11
  • \$\begingroup\$ No one here who can help and explain what the "explain results" mean? \$\endgroup\$ – suntrop Feb 5 '12 at 18:23
2
\$\begingroup\$
  1. 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.
  2. 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.
\$\endgroup\$
  • \$\begingroup\$ Thanks for this hint! Sorry, couldn't answer earlier – was on vacation. EXPLAIN gives me following results for … \$\endgroup\$ – suntrop Jan 23 '12 at 20:30
  • \$\begingroup\$ EXPLAIN gives me following results for … 1) !DB Design Version 1 2) !DB Design Version 2 I really don't know how to tell which one is better. Hope you can help? \$\endgroup\$ – suntrop Jan 23 '12 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.