mysql - 如何优化我的sql代码？

Question

我有下表

联系人

联系人 ID |联系_slug |联系人名字 |联系邮箱 |联系日期添加 |公司 ID |联系人处于事件状态 |联系订阅 |联系人姓氏 |联系公司 |联系推特

联系营销事件

contact_campaign_id |联系人 ID | contact_campaign_created |公司 ID | contact_campaign_sent

bundle_feedback

bundle_feedback_id |捆绑_id、联系人_id |公司 ID |捆绑反馈评级Bundle_feedback_favorite_track_id | Bundle_feedback_favorite_track_id |捆绑反馈支持 |事件_id

捆绑

bundle_id |捆绑名称 | bundle 创建 |公司 ID | bundle 处于事件状态

轨道

track_id |公司 ID |轨道标题

我编写了这个查询，但它运行缓慢，我如何优化这个查询以使其更快？

SELECT SQL_CALC_FOUND_ROWS c.contact_id,
                             c.contact_first_name,
                             c.contact_last_name,
                             c.contact_email,
                             c.contact_date_added,
                             c.contact_company,
                             c.contact_twitter,
                             concat(c.contact_first_name," ", c.contact_last_name) AS fullname,
                             c.contact_subscribed,
                             ifnull(icc.sendCampaignsCount, 0) AS sendCampaignsCount,
                             ifnull(round((ibf.countfeedbacks/sendCampaignsCount * 100),2), 0) AS percentFeedback,
                             ifnull(ibf.bundle_feedback_supporting, 0) AS feedbackSupporting
FROM contacts AS c
LEFT JOIN
  (SELECT c.contact_id,
          count(cc.contact_campaign_id) AS sendCampaignsCount
   FROM contacts AS c
   LEFT JOIN contact_campaigns AS cc ON cc.contact_id = c.contact_id
   WHERE c.company_id = '876'
     AND c.contact_is_active = '1'
     AND cc.contact_campaign_sent = '1'
   GROUP BY c.contact_id) AS icc ON icc.contact_id = c.contact_id
LEFT JOIN
  (SELECT bf.contact_id,
          count(*) AS countfeedbacks,
          bf.bundle_feedback_supporting
   FROM bundle_feedback bf
   JOIN bundles b
   JOIN contacts c
   LEFT JOIN tracks t ON bf.bundle_feedback_favorite_track_id = t.track_id
   WHERE bf.bundle_id = b.bundle_id
     AND bf.contact_id = c.contact_id
     AND bf.company_id='876'
   GROUP BY bf.contact_id) AS ibf ON ibf.contact_id = c.contact_id
WHERE c.company_id = '876'
  AND contact_is_active = '1'
ORDER BY percentFeedback DESC LIMIT 0, 25;

Answer 1

我做了 2 项改进

1) 删除了两次不必要连接的联系人，并将条件放在最后的 where 条件中。

2) 根据 SQL_CALC_FOUND_ROWS 删除

Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)

SELECT                       c.contact_id,
                             c.contact_first_name,
                             c.contact_last_name,
                             c.contact_email,
                             c.contact_date_added,
                             c.contact_company,
                             c.contact_twitter,
                             concat(c.contact_first_name," ", c.contact_last_name) AS fullname,
                             c.contact_subscribed,
                             ifnull(icc.sendCampaignsCount, 0) AS sendCampaignsCount,
                             ifnull(round((ibf.countfeedbacks/sendCampaignsCount * 100),2), 0) AS percentFeedback,
                             ifnull(ibf.bundle_feedback_supporting, 0) AS feedbackSupporting
FROM contacts AS c
LEFT JOIN
  (SELECT cc.contact_id,
          count(cc.contact_campaign_id) AS sendCampaignsCount
   FROM contact_campaigns
   WHERE cc.contact_campaign_sent = '1'
   GROUP BY cc.contact_id) AS icc ON icc.contact_id = c.contact_id
LEFT JOIN
  (SELECT bf.contact_id,
          count(*) AS countfeedbacks,
          bf.bundle_feedback_supporting
   FROM bundle_feedback bf
   JOIN bundles b
   LEFT JOIN tracks t ON bf.bundle_feedback_favorite_track_id = t.track_id
   WHERE bf.bundle_id = b.bundle_id
   GROUP BY bf.contact_id) AS ibf ON ibf.contact_id = c.contact_id
WHERE c.company_id = '876' and c.contact_is_active = '1'