MySQL查询运行和处理需要3个小时

nnsrf1az  于 2022-10-03  发布在  Mysql
关注(0)|答案(1)|浏览(143)

我有一个查询是在深夜对cron作业运行的。然后通过生成器处理该查询,因为它必须填充另一个数据库,并且我在将其发送到另一个数据库之前进行了一些额外的处理和检查。

我想知道有没有什么可以让我加快这个查询的速度,并希望它作为一个单一的查询。或者,我是否会被迫创建其他查询并连接PHP中的数据?这将查询主MAUTIC数据库。

SELECT  c.id as "campaign_id",
        c.created_by_user,
        c.name,
        c.date_added,
        c.date_modified,
        (SELECT DISTINCT COUNT(cl.lead_id)) as number_of_leads,
        GROUP_CONCAT(lt.tag) as tags,
        cat.title as category_name,
        GROUP_CONCAT(ll.name) as segment_name,
        GROUP_CONCAT(emails.name) as email_name,
        CASE WHEN c.is_published = 1 THEN "Yes" ELSE "No" END AS "published",
        CASE WHEN c.publish_down > now() THEN "Yes" 
             WHEN c.publish_down > now() AND c.is_published = 0 THEN "Yes" 
             ELSE "No" END AS "expired"
FROM campaigns c 
    LEFT JOIN campaign_leads cl ON cl.campaign_id = c.id
    LEFT JOIN lead_tags_xref ltx on cl.lead_id = ltx.lead_id 
    LEFT JOIN lead_tags lt on ltx.tag_id = lt.id 
    LEFT JOIN categories cat on c.category_id = cat.id 
    LEFT JOIN lead_lists_leads llist on cl.lead_id = llist.lead_id 
    LEFT JOIN lead_lists ll on llist.leadlist_id = ll.id 
    LEFT JOIN email_list_xref el on ll.id = el.leadlist_id 
    LEFT JOIN emails on el.email_id = emails.id 
GROUP BY c.id;

以下是解释https://prnt.sc/qQtUaLK3FIpQ的图像

定义活动表:https://prnt.sc/6JXRGyMsWpcd

Campaign_Leads表https://prnt.sc/pOq0_SxW2spe

Lead_tag_xref表https://prnt.sc/oKYn92O82gHL

Lead_tag表https://prnt.sc/ImH81ECF6Ly1

类别表https://prnt.sc/azQj_Xwq3dw9

Lead_List_Lead表https://prnt.sc/x5C5fiBFP2N7

Lead_Listers表https://prnt.sc/bltkM0f3XeaH

电子邮件列表xref表https://prnt.sc/kXABVJSYWEUI

电子邮件表https://prnt.sc/7fZcBir1a6QT

我预计只完成871行,我已经确定连接可以非常大,在数万个。

dgiusagp

dgiusagp1#

似乎您有一个无用的SELECT DISTINCT..您是否正在寻找计数(DISTINCT..)
这样,您就可以避免在Main SELECT中为每一行嵌套SELECT。

SELECT  c.id as "campaign_id",
        c.created_by_user,
        c.name,
        c.date_added,
        c.date_modified,
        COUNT(DISTINCT cl.lead_id) as number_of_leads,
        GROUP_CONCAT(lt.tag) as tags,
        cat.title as category_name,
        GROUP_CONCAT(ll.name) as segment_name,
        GROUP_CONCAT(emails.name) as email_name,
        CASE WHEN c.is_published = 1 THEN "Yes" ELSE "No" END AS "published",
        CASE WHEN c.publish_down > now() THEN "Yes" 
             WHEN c.publish_down > now() AND c.is_published = 0 THEN "Yes" 
             ELSE "No" END AS "expired"
FROM campaigns c 
    LEFT JOIN campaign_leads cl ON cl.campaign_id = c.id
    LEFT JOIN lead_tags_xref ltx on cl.lead_id = ltx.lead_id 
    LEFT JOIN lead_tags lt on ltx.tag_id = lt.id 
    LEFT JOIN categories cat on c.category_id = cat.id 
    LEFT JOIN lead_lists_leads llist on cl.lead_id = llist.lead_id 
    LEFT JOIN lead_lists ll on llist.leadlist_id = ll.id 
    LEFT JOIN email_list_xref el on ll.id = el.leadlist_id 
    LEFT JOIN emails on el.email_id = emails.id 
GROUP BY c.id;

无论如何,请确保您有一个适当的综合指数

table campaign_leads columns campaign_id, lead_id
table lead_tags_xref columns lead_id, tag_id
table lead_lists_leads columns lead_id, leadlist_id
table email_list_xref columns leadlist_id, email_id

相关问题