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.
Best Answer
There are at least three parts of your query that will or may benefit from rewriting:
These five correlated subqueries in the SELECT clause are retrieving data from the same table based on the same condition:
You could rewrite them by introducing a left join to
company_profilein your main FROM clause and referencing just the columns in the main SELECT. But wait, you already have such a join:For some reason you are not using it. You can use it by rewriting the above five columns like this:
(The
ASkeyword is unnecessary. I use it because in my opinion it improves readability, but you are free to omit it.)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:
Rewriting these will require more than just and outer join in order to preserve the same output format.
First of all, this join
probably returns data like this:
But you want them in a single row, like this:
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):
outer-join to them in the main FROM clause:
and finally just reference the pivoted column names in the main SELECT clause instead of those correlated subqueries:
Finally, the correlated subqueries in this group:
all have the same pattern: they all use
COUNT(*)to determine whether to displayYESorNO. 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:Alternatively you could be returning just the word
YESand just a single row (usingLIMIT 1) in each case, substituting aNOfor a possible null result with IFNULL or COALESCE: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.