How can I optimize my sql code?

Question

I have following tables

contacts

contact_id | contact_slug | contact_first_name | contact_email | contact_date_added | company_id | contact_is_active | contact_subscribed | contact_last_name | contact_company | contact_twitter

contact_campaigns

contact_campaign_id | contact_id | contact_campaign_created | company_id | contact_campaign_sent

bundle_feedback

bundle_feedback_id | bundle_id, contact_id | company_id | bundle_feedback_rating | bundle_feedback_favorite_track_id | bundle_feedback_supporting | campaign_id

bundles

bundle_id | bundle_name | bundle_created | company_id | bundle_is_active

tracks

track_id | company_id | track_title

I wrote this query, but it works slowly, how can I optimize this query to make it faster ?

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

I have done 2 improvements

1) Removed the contacts which is getting joined unnecessarily twice and put the condition at the final where condition.

2) Removed as per 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'

Answer 2

First, you are not identifying any indexes you have to optimize the query. That said, I would ensure you have at least the following composite / covering indexes.

table              index
contacts           ( company_id, contact_is_active )
contact_campaigns  ( contact_id, contact_campaign_sent )
bundle_feedback    ( contact_id, bundle_feedback_supporting )

Next, as noted in other answer, unless you really need how many rows qualified, remove the "SQL_CALC_FOUND_ROWS".

In your first left-join (icc), you do a left-join on contact_campaigns (cc), but then throw into your WHERE clause an "AND cc.contact_campaign_sent = '1'" which turns that into an INNER JOIN. At the outer query level, these would result in no matching record and thus NULL for your percentage calculations.

In your second left-join (ibf), you are doing a join to the tracks table, but not utilizing anything from it. Also, you are joining to the bundles table but not using anything from there either -- unless you are getting multiple rows in the bundles and tracks tables which would result in a Cartesian result and possibly overstate your "CountFeedbacks" value. You also do not need the contacts table as you are not doing anything else with it, and the feedback table has the contact ID basis your are querying for. Since that is only grouped by the contact_id, your "bf.bundle_feedback_supporting" is otherwise wasted. If you want counts of feedback, just count from that table per contact ID and remove the rest. (also, the joins should have the "ON" clauses instead of within the WHERE clause for consistency)

Also, for your supporting feedback, the data type and value are unclear, so I implied as a Yes or No and have a SUM() based on how many are supporting. So, a given contact may have 100 records but only 37 are supporting. This gives you 1 record for the contact having BOTH values 100 and 37 respectively and not lost in a group by based on the first entry found for the contact.

I would try to summarize your query to below:

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 / icc.sendCampaignsCount * 100),2), 0) AS percentFeedback,
      ifnull(ibf.SupportCount, 0) AS feedbackSupporting
   FROM 
      contacts AS c

         LEFT JOIN 
         ( SELECT 
                 c.contact_id,
                 count(*) AS sendCampaignsCount
              FROM 
                 contacts AS c
                    JOIN contact_campaigns AS cc 
                       ON c.contact_id = cc.contact_id
                      AND cc.contact_campaign_sent = '1'
              WHERE 
                     c.company_id = '876'
                 AND c.contact_is_active = '1'
              GROUP BY 
                 c.contact_id) AS icc 
            ON c.contact_id = icc.contact_id

         LEFT JOIN
         ( SELECT 
                 bf.contact_id,
                 count(*) AS countfeedbacks,
                 SUM( case when bf.bundle_feedback_supporting = 'Y'
                           then 1 else 0 end ) as SupportCount
              FROM 
                 contacts AS c
                    JOIN bundle_feedback bf
                       ON c.contact_id = bf.contact_id
              WHERE 
                     c.company_id = '876'
                 AND c.contact_is_active = '1'
              GROUP BY 
                 bf.contact_id) AS ibf 
            ON c.contact_id = ibf.contact_id
   WHERE 
          c.company_id = '876'
      AND c.contact_is_active = '1'
   ORDER BY 
      percentFeedback DESC LIMIT 0, 25;