Mysql – How to improve this nested MySQL query to avoid redundant WHERE statement

MySQL

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.

Best Answer

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 ;

Related Solutions

MySql – How to improve this query

What are you trying to achieve here? I am not a MySQL programmer; however, as I understand SQL in general, every column that is not listed in the GROUP BY clause must be part of an aggregate like

SELECT
    MAX(id) AS max_id,
    chain, subchain, job_type
FROM
    `job_logs`
WHERE
    user_id = ? AND
    workflow_state = ?
GROUP BY
    chain, subchain, job_type

SELECT * in a grouping query will not work in most (if not all) SQL implementations.

UPDATE

In order to get the row with the highest id for each group you would have to embed the query above in an "outer" query.

SELECT *
FROM `job_logs`
WHERE id IN (
    SELECT
        MAX(id) AS max_id
    FROM
        `job_logs`
    WHERE
        user_id = ? AND
        workflow_state = ?
    GROUP BY
        chain, subchain, job_type 
)

This query is deterministic and should work with most SQL dialects.

Some query engines perform better with joins than with "IN subquery". You can give this a try

SELECT A.*
FROM
    `job_logs` A
    INNER JOIN (
        SELECT
            MAX(id) AS max_id
        FROM
            `job_logs`
        WHERE
            user_id = ? AND
            workflow_state = ?
        GROUP BY
            chain, subchain, job_type) B
    ON A.id = B.max_id;

Mysql – How to improve this query

First, since you already have a UNIQUE index that contains the user_id, you should be able to get rid of the id field, and use the UNIQUE index as the PRIMARY KEY:

CREATE TABLE `twitter_relationships` (
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index`
    (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
PARTITION BY HASH (user_id) PARTITIONS 1000;

Unfortunately, while this removes an index, it may increase storage requirements, due to the way that InnoDB indexes data. See "How Secondary Indexes Relate to the Clustered Index" in http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html

Second, while the source_and_target index has two of the three fields in your WHERE clause, MySQL will have to do an additional read to find the relationship_status.

Therefore, to improve performance, create an index that includes all three fields in your WHERE clause:

CREATE INDEX user_source_status ON twitter_relationships
    (`user_id`,`source_twitter_id`,`relationship_status`);

Then, if MySQL doesn't use this index automatically, you can force using it, with:

SELECT target_twitter_id 
 FROM `twitter_relationships` FORCE INDEX (user_source_status)
WHERE (`twitter_relationships`.`user_id` = ? 
   AND `twitter_relationships`.`source_twitter_id` = ?
   AND `twitter_relationships`.`relationship_status` = ?) 
LIMIT ?, ?

Lastly, you're missing the UNSIGNED attribute on the id, user_id, source_twitter_id, and target_twitter_id fields. I'm guessing these fields will never store negative values, so it would make sense to make them UNSIGNED.

Best Answer

Related Solutions

MySql – How to improve this query

Mysql – How to improve this query

Related Question