0

This is my query:

SELECT          a.z_companyid_pk "z_companyid_pk"      ,
                a.company_name "Client"                ,
                a.display_name "Display_Name"          ,
                a.z_parentcompanyid_fk "Parent_Company",
                a.z_resellerid_fk "Reseller"           ,
                (SELECT company_name
                FROM    company_mst
                WHERE   z_companyid_pk= a.z_resellerid_fk
                )
                "Reseller_name",
                (SELECT IF(COUNT(*)>=1,"YES","NO")
                FROM    webprofile
                WHERE   z_boxid_fk IN
                        (SELECT z_boxid_pk
                        FROM    box_mst
                        WHERE   z_companyid_fk=a.z_companyid_pk
                        )
                )
                "V1_Website",
                (SELECT IF(COUNT(*)>=1,"YES","NO")
                FROM    webapp_mst
                WHERE   z_companyid_fk=a.z_companyid_pk
                )
                "V2_Website",
                (SELECT IF(COUNT(*)>0,"YES","NO")
                FROM    widget_mst
                WHERE   widget_status=1
                AND     type IN("templated-menu",
                                "menus")
                AND     z_companyid_fk=a.z_companyid_pk
                )
                "Widgets_with_my_menu",
                (SELECT IF(COUNT(imagehtml)>0,"YES","NO")
                FROM    image_mst
                WHERE   LENGTH(imagehtml)>0
                AND     z_companyid_fk   =a.z_companyid_pk
                )
                "HTML_menus",
                (SELECT         MAX(login_datetime)
                FROM            loginlog_mst a
                                LEFT OUTER JOIN contact_mst b
                                ON              a.z_contactid_fk=b.z_contactid_pk
                WHERE           b.z_companyid_fk                =a.z_companyid_pk
                )
                "LCI"                    ,
                k.z_boxid_pk "DMB_Box_ID",
                k.box_name               ,
                (SELECT box_lastcheck
                FROM    box_online
                WHERE   z_boxid_fk=k.z_boxid_pk
                )
                "LCI_last_Checked_in_Date",
                (SELECT websitelink
                FROM    company_profile
                WHERE   z_companyid_fk=a.z_companyid_pk
                )
                "Website",
                (SELECT company_email
                FROM    company_profile
                WHERE   z_companyid_fk=a.z_companyid_pk
                )
                "Primary_Contact_Email"                                                          ,
                                concat(a.company_address_line1,a.company_address_line2) "address",
                (SELECT city_name
                FROM    city_mst
                WHERE   z_cityid_pk = a.z_cityid_fk
                )
                "city",
                (SELECT state_name
                FROM    state_mst
                WHERE   z_stateid_pk = a.z_stateid_fk
                )
                "Province" ,
                (SELECT country_name
                FROM    country_mst
                WHERE   z_countryid_pk=z_countryid_fk
                )
                "country_name"                        ,
                a.company_postalcode "zip_postal_code",
                (SELECT telephone
                FROM    company_profile
                WHERE   z_companyid_fk=a.z_companyid_pk
                )
                "company_telephone"   ,
                a.z_timezoneid_fk     ,
                b.timezone "time_Zone",
                (SELECT facebooklink
                FROM    company_profile
                WHERE   z_companyid_fk=a.z_companyid_pk
                )
                "facebook",
                (SELECT twitterlink
                FROM    company_profile
                WHERE   z_companyid_fk=a.z_companyid_pk
                )
                "twitter",
                (SELECT    bizurl
                FROM       user_settings us
                           INNER JOIN asset_mst am
                           ON         (
                                                 us.userid=am.userid
                                      )
                WHERE      us.isiteid       =1
                AND        am.z_companyid_fk=a.z_companyid_pk
                )
                "Yelp",
                (SELECT    bizurl
                FROM       user_settings us
                           INNER JOIN asset_mst am
                           ON         (
                                                 us.userid=am.userid
                                      )
                WHERE      us.isiteid       =2
                AND        am.z_companyid_fk=a.z_companyid_pk
                )
                "Urbanspoon",
                (SELECT    bizurl
                FROM       user_settings us
                           INNER JOIN asset_mst am
                           ON         (
                                                 us.userid=am.userid
                                      )
                WHERE      us.isiteid       =3
                AND        am.z_companyid_fk=a.z_companyid_pk
                )
                "Trip_Advisor",
                (SELECT    bizurl
                FROM       user_settings us
                           INNER JOIN asset_mst am
                           ON         (
                                                 us.userid=am.userid
                                      )
                WHERE      us.isiteid       =4
                AND        am.z_companyid_fk=a.z_companyid_pk
                )
                "Citysearch",
                (SELECT    bizurl
                FROM       user_settings us
                           INNER JOIN asset_mst am
                           ON         (
                                                 us.userid=am.userid
                                      )
                WHERE      us.isiteid       =5
                AND        am.z_companyid_fk=a.z_companyid_pk
                )
                "Open_Table",
                (SELECT    bizurl
                FROM       user_settings us
                           INNER JOIN asset_mst am
                           ON         (
                                                 us.userid=am.userid
                                      )
                WHERE      us.isiteid       =6
                AND        am.z_companyid_fk=a.z_companyid_pk
                )
                "ZAGAT",
                (SELECT    bizurl
                FROM       user_settings us
                           INNER JOIN asset_mst am
                           ON         (
                                                 us.userid=am.userid
                                      )
                WHERE      us.isiteid       =32
                AND        am.z_companyid_fk=a.z_companyid_pk
                )
                "Zomato"                           ,
                d.z_contactid_fk "Email_contact_ID",
                d.username "Email_User_Name"       ,
                d.contact_firstname                ,
                d.contact_lastname                 ,
                d.status                           ,
                (SELECT MAX(login_datetime)
                FROM    loginlog_mst
                WHERE   z_contactid_fk=d.z_contactid_fk
                )
                "last_login_date",
                (SELECT GROUP_CONCAT(groupname)
                FROM    contact_group_mst
                WHERE   z_companyid_fk=a.z_companyid_pk
                )
                "group_name"
FROM            company_mst a
                LEFT OUTER JOIN timezone_mst b
                ON              a.z_timezoneid_fk=b.z_timezoneid_pk
                LEFT OUTER JOIN company_profile c
                ON              a.z_companyid_pk=c.z_companyid_fk
                LEFT OUTER JOIN
                                (SELECT         v.z_contactid_fk   ,
                                                v.z_companyid_fk   ,
                                                w.username         ,
                                                w.contact_firstname,
                                                w.contact_lastname ,
                                                IF(contact_wtaccess=1,"Veryfied","Not Veryfied") "status"
                                FROM            priviledge_mst v
                                                LEFT OUTER JOIN contact_mst w
                                                ON              v.z_contactid_fk=w.z_contactid_pk
                                WHERE           w.type                         <>"Staff"
                                )
                                d
                ON              a.z_companyid_pk=d.z_companyid_fk
                LEFT OUTER JOIN
                                (SELECT    z_boxid_pk,
                                           box_name  ,
                                           asset_mst.z_companyid_fk
                                FROM       box_mst
                                           INNER JOIN asset_mst
                                           ON        (
                                                                 box_mst.userid_fk = asset_mst.userid
                                                      )
                                WHERE      asset_mst.status = 0
                                AND        type             ="menubox"
                                )
                                k
                ON              a.z_companyid_pk=k.z_companyid_fk
WHERE           a.status                       <> 0
AND             a.z_companyid_pk IN(101089,104001,103863)
ORDER BY        a.company_name;

This query takes a minimum of 1 minute to execute on my remote MySQL server. Since I am new on MySQL, I don't know much about optimizing queries, so please help me by suggesting some techniques or modifications or any tips that I can apply on this query to improve performance.

15

There are at least three parts of your query that will or may benefit from rewriting:

  1. These five correlated subqueries in the SELECT clause are retrieving data from the same table based on the same condition:

    (select websitelink from company_profile  where z_companyid_fk=a.z_companyid_pk) "Website",
    (select company_email from company_profile  where z_companyid_fk=a.z_companyid_pk) "Primary_Contact_Email",
    (select telephone from company_profile  where z_companyid_fk=a.z_companyid_pk) "company_telephone",
    (select facebooklink from company_profile  where z_companyid_fk=a.z_companyid_pk) "facebook", 
    (select twitterlink from company_profile  where z_companyid_fk=a.z_companyid_pk) "twitter", 
    

    You could rewrite them by introducing a left join to company_profile in your main FROM clause and referencing just the columns in the main SELECT. But wait, you already have such a join:

    LEFT OUTER JOIN company_profile c on a.z_companyid_pk=c.z_companyid_fk 
    

    For some reason you are not using it. You can use it by rewriting the above five columns like this:

    c.websitelink   AS "Website",
    c.company_email AS "Primary_Contact_Email",
    c.telephone     AS "company_telephone",
    c.facebooklink  AS "facebook", 
    c.twitterlink   AS "twitter", 
    

    (The AS keyword is unnecessary. I use it because in my opinion it improves readability, but you are free to omit it.)

  2. This series of correlated subqueries is pulling data from the same rowset as well, but each subquery has an additional condition, which makes this a different case from the previous one:

    (SELECT  bizurl FROM user_settings us  INNER JOIN asset_mst am ON (us.userid=am.userid)  where  us.isiteid=1 and am.z_companyid_fk=a.z_companyid_pk ) "Yelp",
    (SELECT  bizurl FROM user_settings us  INNER JOIN asset_mst am ON (us.userid=am.userid)  where  us.isiteid=2 and am.z_companyid_fk=a.z_companyid_pk ) "Urbanspoon",
    (SELECT  bizurl FROM user_settings us  INNER JOIN asset_mst am ON (us.userid=am.userid)  where  us.isiteid=3 and am.z_companyid_fk=a.z_companyid_pk ) "Trip_Advisor",
    (SELECT  bizurl FROM user_settings us  INNER JOIN asset_mst am ON (us.userid=am.userid)  where  us.isiteid=4 and am.z_companyid_fk=a.z_companyid_pk ) "Citysearch",
    (SELECT  bizurl FROM user_settings us  INNER JOIN asset_mst am ON (us.userid=am.userid)  where  us.isiteid=5 and am.z_companyid_fk=a.z_companyid_pk ) "Open_Table",
    (SELECT  bizurl FROM user_settings us  INNER JOIN asset_mst am ON (us.userid=am.userid)  where  us.isiteid=6 and am.z_companyid_fk=a.z_companyid_pk ) "ZAGAT",
    (SELECT  bizurl FROM user_settings us  INNER JOIN asset_mst am ON (us.userid=am.userid)  where  us.isiteid=32 and am.z_companyid_fk=a.z_companyid_pk ) "Zomato",
    

    Rewriting these will require more than just and outer join in order to preserve the same output format.

    First of all, this join

    user_settings us  INNER JOIN asset_mst am ON (us.userid=am.userid)
    

    probably returns data like this:

    ...  am.z_companyid_fk  us.isiteid  us.bizurl                ...
    ---  --------------     ----------  -----------------------  ---
    ...  some_comp_id       1           some-Yelp-URL            ...
    ...  some_comp_id       2           some-Urbanspoon-URL-URL  ...
    ...  some_comp_id       3           some-Trip_Advisor-URL    ...
    ...  some_comp_id       4           some-Citysearch-URL      ...
    ...  some_comp_id       5           some-Open_Table-URL      ...
    ...  some_comp_id       6           some-ZAGAT-URL           ...
    ...  some_comp_id       32          some-Zomato-URL          ...
    ...  ...                ...         ...                      ...
    

    But you want them in a single row, like this:

    ...  am.z_companyid_fk  Yelp           Urbanspoon               Trip_Advisor           Citysearch           Open_Table          ZAGAT           Zomato
    ---  -----------------  -------------  -----------------------  ---------------------  -------------------  ------------------  --------------  ---------------
    ...  some_comp_id       some-Yelp-URL  some-Urbanspoon-URL-URL  some-Trip_Advisor-URL  some-Citysearch-URL  some-Open_Table-URL some-ZAGAT-URL  some-Zomato-URL
    ...  ...                ...            ...                      ...                    ...                  ...                 ...             ...
    

    The process of turning the rows like this is called pivoting. So, you

    • pivot the results of the join (in MySQL you can do that using conditional aggregation):

      SELECT
        am.z_companyid_fk,
        MAX( CASE WHEN us.isiteid= 1 THEN bizurl END ) AS "Yelp",
        MAX( CASE WHEN us.isiteid= 2 THEN bizurl END ) AS "Urbanspoon",
        MAX( CASE WHEN us.isiteid= 3 THEN bizurl END ) AS "Trip_Advisor",
        MAX( CASE WHEN us.isiteid= 4 THEN bizurl END ) AS "Citysearch",
        MAX( CASE WHEN us.isiteid= 5 THEN bizurl END ) AS "Open_Table",
        MAX( CASE WHEN us.isiteid= 6 THEN bizurl END ) AS "ZAGAT",
        MAX( CASE WHEN us.isiteid=32 THEN bizurl END ) AS "Zomato"
      FROM
        user_settings us
        INNER JOIN asset_mst am ON (us.userid=am.userid)
      GROUP BY
        am.z_companyid_fk
      
    • outer-join to them in the main FROM clause:

      LEFT JOIN
      (
        SELECT
          am.z_companyid_fk,
        …
        …
        …
        GROUP BY
          am.z_companyid_fk
      ) AS url ON url.z_companyid_fk=a.z_companyid_pk
      
    • and finally just reference the pivoted column names in the main SELECT clause instead of those correlated subqueries:

      url."Yelp",
      url."Urbanspoon",
      url."Trip_Advisor",
      url."Citysearch",
      url."Open_Table",
      url."ZAGAT",
      url."Zomato",
      
  3. Finally, the correlated subqueries in this group:

    (select if(count(*)>=1,"YES","NO") from webprofile where z_boxid_fk in(select z_boxid_pk from box_mst where z_companyid_fk=a.z_companyid_pk)) "V1_Website",
    (select if(count(*)>=1,"YES","NO") from webapp_mst where z_companyid_fk=a.z_companyid_pk) "V2_Website",
    (select if(count(*)>0,"YES","NO") from widget_mst where widget_status=1 and type in("templated-menu","menus") and z_companyid_fk=a.z_companyid_pk) "Widgets_with_my_menu",
    (select if(count(imagehtml)>0,"YES","NO") from image_mst where  length(imagehtml)>0 and  z_companyid_fk=a.z_companyid_pk)"HTML_menus",
    

    all have the same pattern: they all use COUNT(*) to determine whether to display YES or NO. Depending on the DB engine you are using, that may be inefficient. It might be a better idea to use EXISTS instead. So, you could rewrite the above like this:

    IF(EXISTS (select * from webprofile where z_boxid_fk in(select z_boxid_pk from box_mst where z_companyid_fk=a.z_companyid_pk)),       'YES', 'NO') AS "V1_Website",
    IF(EXISTS (select * from webapp_mst where z_companyid_fk=a.z_companyid_pk),                                                           'YES', 'NO') AS "V2_Website",
    IF(EXISTS (select * from widget_mst where widget_status=1 and type in('templated-menu','menus') and z_companyid_fk=a.z_companyid_pk), 'YES', 'NO') AS "Widgets_with_my_menu",
    IF(EXISTS (select * from image_mst  where length(imagehtml)>0 and z_companyid_fk=a.z_companyid_pk),                                   'YES', 'NO') AS "HTML_menus",
    

    Alternatively you could be returning just the word YES and just a single row (using LIMIT 1) in each case, substituting a NO for a possible null result with IFNULL or COALESCE:

    IFNULL( (select 'YES' from webprofile where z_boxid_fk in(select z_boxid_pk from box_mst where z_companyid_fk=a.z_companyid_pk) LIMIT 1),       'NO') AS "V1_Website",
    IFNULL( (select 'YES' from webapp_mst where z_companyid_fk=a.z_companyid_pk LIMIT 1),                                                           'NO') AS "V2_Website",
    IFNULL( (select 'YES' from widget_mst where widget_status=1 and type in('templated-menu','menus') and z_companyid_fk=a.z_companyid_pk LIMIT 1), 'NO') AS "Widgets_with_my_menu",
    IFNULL( (select 'YES' from image_mst where length(imagehtml)>0 and z_companyid_fk=a.z_companyid_pk LIMIT 1),                                    'NO') AS "HTML_menus",
    

There may be other parts worth attention but these three were ones that stood out for me.

In conclusion, I would just like to add that the rewrite in the first bullet point is definitely going to be an improvement to your query as posted in your question. The other two I am less certain about, so I would advise you to try/test them independently.

1
  1. Don't use the construct IN ( SELECT ... ) if you can avoid it; it optimizes very poorly. Usually a JOIN can be done instead.

  2. Avoid having more than one subquery in FROM ( SELECT ... ) JOIN ( SELECT ... ) JOIN .... Again very poor optimization (because of an index on the tmp table). Again try to replace with a JOIN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.