MySQL:使用子查询返回按日期分组的统计信息

Question

我正在从表中返回一些统计信息(在一段时间内)，其中包含几个子查询并按日期时间分组(转换为日期)。尽管该查询有效，但对于包含大约 200 万行的表，大约需要 20 分钟才能完成。日期列上已经有一个索引(不幸的是日期时间)。我怀疑在这种特殊情况下，缓慢的原因是我使用 DATE() 函数首先将日期时间转换为日期以进行比较。关于如何加快速度有什么建议吗？我可以做到这一点的一种方法是向数据库添加另一列，该列在添加时保存每行的日期值，但也许有一些明显的事情我可以做，而不必在第一次添加时重复日期:

 $sql = "
    SELECT
        (SELECT COUNT(`id`) FROM `mg_logs` WHERE `mg_authtype_id`=1 AND `mg_mailbox_id`=? AND DATE(`date`)=DATE(`l`.`date`)) AS `count1`,
        (SELECT COUNT(`id`) FROM `mg_logs` WHERE `mg_authtype_id`=2 AND `mg_mailbox_id`=? AND DATE(`date`)=DATE(`l`.`date`)) AS `count2`,
        (SELECT COUNT(`id`) FROM `mg_logs` WHERE `mg_authtype_id`=3 AND `mg_mailbox_id`=? AND DATE(`date`)=DATE(`l`.`date`)) AS `count3`,
        (SELECT COUNT(`id`) FROM `mg_logs` WHERE `mg_authtype_id`=4 AND `mg_mailbox_id`=? AND DATE(`date`)=DATE(`l`.`date`)) AS `count4`,
        DATE(`l`.`date`) AS `sdate`
    FROM 
        `mg_logs` `l`
    WHERE 
        `l`.`mg_mailbox_id`=?
    GROUP BY DATE(`date`)
    ";

Answer 1

如果您可以添加日期列，那肯定会有帮助。但是，删除子查询也应该有所帮助:

$sql = "
    SELECT
        SUM(CASE WHEN `l`.`mg_authtype_id`=1 THEN 1 ELSE 0 END) AS `count1`,
        SUM(CASE WHEN `l`.`mg_authtype_id`=2 THEN 1 ELSE 0 END) AS `count2`,
        SUM(CASE WHEN `l`.`mg_authtype_id`=3 THEN 1 ELSE 0 END) AS `count3`,
        SUM(CASE WHEN `l`.`mg_authtype_id`=4 THEN 1 ELSE 0 END) AS `count4`,
        DATE(`l`.`date`) AS `sdate`
    FROM 
        `mg_logs` `l`
    WHERE 
        `l`.`mg_mailbox_id`=?
    GROUP BY DATE(`date`)
    ";