MySQL综合索引

MySQL综合索引

这是您的第一个查询：

SELECt A.log_type, count(*) as distinct_count, sum(A.total_count) as total_countfrom (SELECt log_type, count(subscriber_id) as total_count      FROM stats.campaign_logs      WHERe domain = 'xxx' AND campaign_id = '12345' AND log_type IN ('EMAIL_SENT', 'EMAIL_CLICKED', 'EMAIL_OPENED', 'UNSUBSCRIBED') AND  DATE(CONVERT_TZ(log_time,'+00:00','+05:30')) BETWEEN DATE('2015-02-12 00:00:00') AND DATE('2015-02-19 23:59:58')      GROUP BY subscriber_id,log_type) AGROUP BY A.log_type;

最好写成：

      SELECt log_type, count(DISTINCT subscriber_id) as total_count      FROM stats.campaign_logs      WHERe domain = 'xxx' AND campaign_id = '12345' AND log_type IN ('EMAIL_SENT', 'EMAIL_CLICKED', 'EMAIL_OPENED', 'UNSUBSCRIBED') AND  DATE(CONVERT_TZ(log_time, '+00:00', '+05:30')) BETWEEN DATE('2015-02-12 00:00:00') AND DATE('2015-02-19 23:59:58')      GROUP BY log_type;

最好的索引可能是：

campaign_logs(domain, campaign_id, log_type, log_time,subscriber_id)

。这是查询的覆盖索引。前三个键应用于

where

过滤。