How MySql handles composite index
<pre><code>CREATE TABLE campaigns (
  domain varchar(50) DEFAULT NULL,
  campaign_id varchar(50) DEFAULT NULL,
  node_id varchar(50) DEFAULT NULL,
  subscriber_id varchar(50) DEFAULT NULL,
  message varchar(21000) DEFAULT NULL,
  log_time datetime DEFAULT NULL,
  log_type varchar(50) DEFAULT NULL,
  campaign_name varchar(500) DEFAULT NULL

  KEY `campid_domain_logtype_logtime_subid_index` (`campaign_id`,`domain`,`log_type`,`log_time`,`subscriber_id`),
  KEY `domain_logtype_logtime_index` (`domain`,`log_type`,`log_time`)

)
</code></pre>

<p>The above one is schema of our table in MySql. One campaign can have multiple nodes. </p>

<p>The index <code>campid_domain_logtype_logtime_subid_index</code> works fine with reports w.r.t specific campaign.</p>

<p>recently we added node_id into this table, now we need reports w.r.t specific node .</p>

<p>So now I'm planning to change our covering index like following to support both campaign level and node level reports</p>

<p><code>campid_nodeid_domain_logtype_logtime_subid_index</code>.</p>

<p>Example query for reports on specific campaign</p>

<pre><code>SELECT log_type,
        count(DISTINCT subscriber_id) AS count,
        count(subscriber_id) AS total 
FROM stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index) 
where domain = 'aaa' AND campaign_id='12345' AND 
  log_type in ('EMAIL_SENT','EMAIL_OPENED','EMAIL_CLICKED')
  AND log_time BETWEEN CONVERT_TZ('2016-03-13 00:00:00','+01:30','+00:00') AND CONVERT_TZ('2016-04-13 23:59:59','+01:30','+00:00') 
GROUP BY log_type;
</code></pre>

<p>Example query for reports on specific node id of specific campaign</p>

<pre><code>SELECT 
   log_type,
   count(DISTINCT subscriber_id) AS count,
   count(subscriber_id) AS total 
FROM stats.campaign_logs USE INDEX(campid_domain_logtype_logtime_subid_index) 
where domain='aaa' AND campaign_id='12345' AND 
      node_id = '56789' and 
      log_type in ('EMAIL_SENT','EMAIL_OPENED','EMAIL_CLICKED') 
      AND log_time BETWEEN CONVERT_TZ('2016-03-13 00:00:00','+01:30','+00:00') AND CONVERT_TZ('2016-04-13 23:59:59','+01:30','+00:00') 
GROUP BY log_type;
</code></pre>

<p>So my question does this new index satisfy our both kind of queries effectively if not please suggest suitable indexes.</p>

<p><strong>UPDATE:</strong></p>

<p>Data distribution in table</p>

<p>1 Domain can have multiple campaigns for example 20</p>

<p>1 Campaign can have multiple nodes for example 10</p>

<p>1 Campaign can have multiple log types for example 50</p>

<p>1 Campaign can have lot of subscribers for example 100,000</p>

<p>1 Campaign can have lot of log times because we use mysql now() when we store log.</p>

<p>UPDATE </p>

<p>Thanks</p>