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.
