Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question):

tbl_users:
ID | email (all subscribers to our email list)

tbl_mailings:
ID | mail_title (each marketing email)

tbl_sends:
ID | user_id | mailing_id (each user who was sent each mailing)

tbl_opens:
ID | user_id | mailing_id (each user who opened each mailing)

tbl_actions:
ID | user_id | mailing_id (each user who took the target action on each mailing)

I inherited this query that I have been using to get the open rate and action rate for each mailing:

SELECT subq_opens.numopens/subq_sends.numsends as Open_Rate,
       subq_actions.numactions/subq_sends.numsends as Action_Rate,
FROM
(SELECT COUNT(DISTINCT sends.userID) AS numsends
    FROM tbl_sends WHERE mailing_id = 5694) AS subq_sends,

(SELECT COUNT(DISTINCT opens.userID) AS numopens
    FROM tbl_opens WHERE mailing_id = 5694) AS subq_opens,

(SELECT COUNT(DISTINCT actions.userID) AS numactions
    FROM tbl_actions WHERE mailing_id = 5694) AS subq_actions

Not too elegant, but functional, even though I have to change the mailing ID in multiple places every time.
However, now I need to change that WHERE statement to something a great deal more complicated, and I don't want to have to repeat the WHERE statement so many times.

Is there a way I can reformulate this without the redundant WHERE?
I have tried various nesting schemes, but can't seem to find a way to get it right. I can't change anything about the table structure; that is set in stone. Thank you in advance for your help.

share|improve this question

migrated from stackoverflow.com Apr 14 '13 at 11:34

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 4 down vote accepted

Reformulating your query:

SELECT 
    numopens / numsends AS Open_Rate,
    numactions / numsends AS Action_Rate
FROM
    ( SELECT 
          ( SELECT COUNT(DISTINCT s.userID) 
            FROM tbl_sends AS s
            WHERE s.mailing_id = param.mailing_id
          )  
        AS numsends,
          ( SELECT COUNT(DISTINCT o.userID) 
            FROM tbl_opens AS o
            WHERE o.mailing_id = param.mailing_id
          )  
        AS numopens
          ( SELECT COUNT(DISTINCT a.userID) 
            FROM tbl_actions AS a 
            WHERE a.mailing_id = param.mailing_id
          )
        AS numactions
      FROM 
          ( SELECT 
                5694     AS mailing_id,
                417      AS another_parameter,
                'funny'  AS one_more_parameter   
          ) AS param  
    ) AS cte ;
share|improve this answer
    
This works! Thank you! I didn't think of putting the actual mailing ID into a SELECT statement... kept trying to think of a way to do it with WHERE or FROM. Thanks again! –  tarastar42 Apr 13 '13 at 17:21

I guess joining the three table would do the trick:

SELECT 
sub.opens / sub.sends AS open_rate,
sub.actions / sub.sends AS action_rate
FROM
(
SELECT
    COUNT(DISTINCT tbl_sends.userID) AS sends,
    COUNT(DISTINCT tbl_opens.userID) AS opens,
    COUNT(DISTINCT tbl_actions.userID) AS actions
FROM
    tbl_sends
LEFT JOIN tbl_opens
    ON tbl_sends.mailing_id = tbl_opens.mailing_id
LEFT JOIN tbl_actions
    ON tbl_opens.mailing_id = tbl_actions.mailing_id
WHERE
    tbl_sends.mailing_id = 5694
) AS sub;


Another easy way to do this would be:

SET @my_mailing_id = <some id>;
<your original query using @my_mailing_id in WHERE>

However, because MySQL doesn't support arrays/lists in variables, this only works for a single ID.
A workaraound would be:

SET @my_mailing_ids = '1,2,3,6'; -- notice the VARCHAR
<your query with this in WHERE clause:>
... WHERE 0 < FIND_IN_SET(mailing_id, @my_mailing_ids) ... 

This would avoid the JOIN limit - but has really bad performance.

share|improve this answer
    
Makes sense... but this is what happens when I try that: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay –  tarastar42 Apr 13 '13 at 5:19
    
I get that same error if I run just the inner query and take the outer query out entirely. I get it even if I just try to run sends and opens, leaving actions out. What could be wrong? Thanks for your help... and for the formatting help too! –  tarastar42 Apr 13 '13 at 5:22
    
Here the modified SQL I am trying (this time with my actual names - the ones in my original question were changed for clarity): SELECT COUNT(DISTINCT core_usermailing.user_id) AS num_sent, COUNT(DISTINCT core_open.user_id) AS num_opened FROM core_usermailing LEFT JOIN core_open ON core_open.mailing_id = core_usermailing.mailing_id WHERE core_usermailing.mailing_id IN (1524, 1525, 1526, 1527, 1528) Advice? Thanks again! –  tarastar42 Apr 13 '13 at 5:43
    
@user2276596 This might be happeing because your tables are very large. How much rows do you have in them? Your server could also be misconfigured - use SELECT @@MAX_JOIN_SIZE; to determine the maximum join size.You might then want to have an index on the mailing_id columns of your tables. –  Lukas Apr 13 '13 at 13:03
    
@user2276596 When you can run slow queries in your environment without harming production, you might want to have MySQL explain your query. Do SET SQL_BIG_SELECTS=1; EXPLAIN <query>. The first statement will allow you to do big joins, the latter one will give you an insight into which rows the engine actually accesses. –  Lukas Apr 13 '13 at 13:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.