mysql - 改进 SQL 查询，以便我只能获取已插入的日期之后的日期

Question

我正在尝试改进我的 SQL 查询，以便我只能从 f_facts 表中获取最新信息，并通过 date_id 检查该信息是否是新的

由于某种原因，此查询对我不起作用:

REPLACE INTO `r_views`
        SELECT network_id, type_id, topic_id, COUNT(*), date_id, 
        SUM( IF (`d_refer`.`refer` LIKE '%facebook%', 1,0) ) AS `facebook`, 
        SUM( IF (`d_refer`.`refer` LIKE '%twitter%', 1,0) ) AS `twitter`, 
        SUM( IF ( (`d_refer`.`refer` LIKE '%hotmail%' or `d_refer`.`refer` LIKE '%live%' or `d_refer`.`refer` LIKE '%gmail%' or `d_refer`.`refer` LIKE '%mail.%'), 1,0) ) AS `email`, 
        SUM( IF (`d_refer`.`refer` LIKE '%google%', 1,0) ) AS `google`      

        FROM `f_facts` 
        LEFT JOIN `d_date` ON `f_facts`.`date_id` = `d_date`.`id` 
        LEFT JOIN `d_refer` ON `f_facts`.`refer_id` = `d_refer`.`id` 

        WHERE `action_id`=3 AND `type_id` != 17 AND `date_id` > (SELECT `date_id` FROM `r_views` ORDER BY `date_id` DESC LIMIT 1) GROUP BY topic_id, date_id

这是我的旧 SQL 查询，它从 f_facts 表中读取所有数据，效率不高:

REPLACE INTO `r_views`
        SELECT network_id, type_id, topic_id, COUNT(*), date_id, 
        SUM( IF (`d_refer`.`refer` LIKE '%facebook%', 1,0) ) AS `facebook`, 
        SUM( IF (`d_refer`.`refer` LIKE '%twitter%', 1,0) ) AS `twitter`, 
        SUM( IF ( (`d_refer`.`refer` LIKE '%hotmail%' or `d_refer`.`refer` LIKE '%live%' or `d_refer`.`refer` LIKE '%gmail%' or `d_refer`.`refer` LIKE '%mail.%'), 1,0) ) AS `email`, 
        SUM( IF (`d_refer`.`refer` LIKE '%google%', 1,0) ) AS `google`      

        FROM `f_facts` 
        LEFT JOIN `d_date` ON `f_facts`.`date_id` = `d_date`.`id` 
        LEFT JOIN `d_refer` ON `f_facts`.`refer_id` = `d_refer`.`id` 

        WHERE `action_id`=3 AND `type_id` != 17 GROUP BY topic_id, date_id 

Answer 1

我认为你可以在三点上增强查询:

1) 电子邮件列您可以使用它来代替 OR:

SUM( IF ( (`d_refer`.`refer` LIKE '%live%' or `d_refer`.`refer` LIKE '%mail.%'), 1,0) ) AS `email`

2)你真的需要这两个连接吗？在我看来，它们有点多余。

3) 添加日期索引