Optimisation of query

[javedsai]

Hi All,

I need to optimise the below query. The below query takes 13 seconds to run on live. Kindly, suggest how I can optimise this query and how can I index the table? The query is as mentioned below:-

SELECT *, a.forxmlfeed, a.lastupdated, a.mark_for_deletion, a.is_mixed_use, p.value as projtypeformixuse,
  b.active as enquiry_active_status, g.countries,
  a.xmllastupdated as xmllastupdated, 
  a.lastupdated as lastupdated,
  a.id as refid,
  a.id as property_info_id, 
  d.customer_types as for_type,
  a.description as descr, 
  a.swimming_pool as pool,
  a.appliances as appliances,
  c.id as projectid, 
  g.areaname as area,
  c.project_name as project,
  n.id as projecttypeid,
  c.project_types as projecttypeid1,  
  n.value as propertytype,
  p.id as projecttypeid_mixuse,
  p.value as proptypeifmixuse,
  n.value as propertytype,
  a.title_deed as titledeed,
  a.finance_avail as mortgaged,
  k.beds as beds, 
  f.value as unitmodel,
  k.bua as size, 
  a.unitno as unitno,
  a.street_floor as streetflr,
  a.plot_area as plot_area,
  a.plot_area as plotarea,
  k.view as view,
  k.study as study,
  k.maid as maid,
  k.parking as parking,
  a.web_title as web_title, 
  a.list_price as price,  
  t.cheques as cheques,
  i.email1 as contactemail, 
  b.active as status, 
  s.value as city, 
  s.Countryname as country,
  i.mobile1 as contactnumber,
  i.firstname as agentfname,
  a.rented as rented,
  a.annual_rent as annualrent,
  a.rented_till as rentedtill,
  i.user as agent,
  a.no_proptypes as with_proptype                   
  FROM property_info a 
  LEFT JOIN areas_enquired b ON a.areas_enquired=b.id
  LEFT JOIN projects c ON a.projects=c.id
  LEFT JOIN enquiry_info d ON b.enquiry_info=d.id
  LEFT JOIN customer_types e ON d.customer_types=e.id
  LEFT JOIN proptype_models j ON (c.id=j.projects && j.unitmodels=a.unitmodels)
  LEFT JOIN property_type k ON k.id=j.property_type
  LEFT JOIN project_types n ON n.id=c.project_types
  LEFT JOIN project_types p ON p.id=a.is_mixed_use
  LEFT JOIN unitmodels f ON a.unitmodels=f.id
  LEFT JOIN areas g ON b.areas=g.id
  LEFT JOIN areas_enquired h ON (d.id=h.enquiry_info && b.id=h.id)
  LEFT JOIN agents i ON d.agents=i.id 
  LEFT JOIN emirates s ON g.emirates=s.id
  LEFT JOIN tenant_info t ON (t.areas_enquired=a.areas_enquired && t.project_types=n.id )
  WHERE c.project_types IN ('0','1','2','3','4','5','6','7','8','9','10','11')    && g.countries='1'  && d.customer_types IN ('2','4','6','8','13')   ORDER BY a.xmllastupdated DESC

Thanks in advance..

[pritaeas]

14 left joins... Have you tried EXPLAIN ? Are you using indexes, foreign keys ? Apart from all that, if you can, put the most restrictive restriction first (to compact the result set early on).

[javedsai]

yes there are 14 left joins. If I want to Index it then, should I apply index to project_types (tbl projects), countries (tbl areas) & customer_types (tbl enquiry_info)? Kindly suggest, I'm new to Indexing

[javedsai]

Also when I run this long query then, I can't see Explain option

[pritaeas]

Usually you can assume that you'll need an index on the fields you are joining. EXPLAIN can guide you in that. An overkill of indexes is not always helpful, and can slow things down even more.

The restrictions in the WHERE should IMO be moved to the matching join.

[javedsai]

U mean I need to index those fields which are used for joining? I could not understand this sentence "The restrictions in the WHERE should IMO be moved to the matching join."

Also, when I ran the above query I can't see 'EXPLAIN' option.

[pritaeas]

You have three conditions in the WHERE that can be moved up to the corresponding LEFT JOIN ON.

http://dev.mysql.com/doc/refman/5.6/en/explain.html

[javedsai]

1   SIMPLE  a   ALL     NULL    NULL    NULL    NULL    3669    Using temporary; Using filesort
1   SIMPLE  c   eq_ref  PRIMARY,id  PRIMARY     4   bluechi9_main.a.projects    1   Using where
1   SIMPLE  b   eq_ref  PRIMARY     PRIMARY     4   bluechi9_main.a.areas_enquired  1   Using where
1   SIMPLE  d   eq_ref  PRIMARY     PRIMARY     4   bluechi9_main.b.enquiry_info    1   Using where
1   SIMPLE  e   eq_ref  PRIMARY     PRIMARY     4   bluechi9_main.d.customer_types  1   
1   SIMPLE  j   ALL     NULL    NULL    NULL    NULL    3908    
1   SIMPLE  k   eq_ref  PRIMARY     PRIMARY     4   bluechi9_main.j.property_type   1   
1   SIMPLE  n   eq_ref  PRIMARY,id  PRIMARY     4   bluechi9_main.c.project_types   1   
1   SIMPLE  p   eq_ref  PRIMARY,id  PRIMARY     4   bluechi9_main.a.is_mixed_use    1   
1   SIMPLE  g   eq_ref  PRIMARY     PRIMARY     4   bluechi9_main.b.areas   1   Using where
1   SIMPLE  f   eq_ref  PRIMARY     PRIMARY     4   bluechi9_main.a.unitmodels  1   
1   SIMPLE  h   eq_ref  PRIMARY     PRIMARY     4   bluechi9_main.a.areas_enquired  1   
1   SIMPLE  i   eq_ref  PRIMARY     PRIMARY     4   bluechi9_main.d.agents  1   
1   SIMPLE  s   eq_ref  PRIMARY     PRIMARY     4   bluechi9_main.g.emirates    1   
1   SIMPLE  t   ALL     NULL    NULL    NULL    NULL    863     

The above is the explaination of the sql query. Can u plz help me in optimisation. Also, I moved 3 where conditions with the corresponding left join but no. of records differ. Please find below the query:-

SELECT *, a.forxmlfeed, a.lastupdated, a.mark_for_deletion, a.is_mixed_use, p.value as projtypeformixuse,
  b.active as enquiry_active_status, g.countries,
  a.xmllastupdated as xmllastupdated, 
  a.lastupdated as lastupdated,
  a.id as refid,
  a.id as property_info_id, 
  d.customer_types as for_type,
  a.description as descr, 
  a.swimming_pool as pool,
  a.appliances as appliances,
  c.id as projectid, 
  g.areaname as area,
  c.project_name as project,
  n.id as projecttypeid,
  c.project_types as projecttypeid1,  
  n.value as propertytype,
  p.id as projecttypeid_mixuse,
  p.value as proptypeifmixuse,
  n.value as propertytype,
  a.title_deed as titledeed,
  a.finance_avail as mortgaged,
  k.beds as beds, 
  f.value as unitmodel,
  k.bua as size, 
  a.unitno as unitno,
  a.street_floor as streetflr,
  a.plot_area as plot_area,
  a.plot_area as plotarea,
  k.view as view,
  k.study as study,
  k.maid as maid,
  k.parking as parking,
  a.web_title as web_title, 
  a.list_price as price,  
  t.cheques as cheques,
  i.email1 as contactemail, 
  b.active as status, 
  s.value as city, 
  s.Countryname as country,
  i.mobile1 as contactnumber,
  i.firstname as agentfname,
  a.rented as rented,
  a.annual_rent as annualrent,
  a.rented_till as rentedtill,
  i.user as agent,
  a.no_proptypes as with_proptype                   
  FROM property_info a 
  LEFT JOIN areas_enquired b ON a.areas_enquired=b.id
  LEFT JOIN projects c ON (a.projects=c.id && c.project_types IN ('0','1','2','3','4','5','6','7','8','9','10','11') )
  LEFT JOIN enquiry_info d ON (b.enquiry_info=d.id && d.customer_types IN ('2','4','6','8','13') )
  LEFT JOIN customer_types e ON d.customer_types=e.id
  LEFT JOIN proptype_models j ON (c.id=j.projects && j.unitmodels=a.unitmodels)
  LEFT JOIN property_type k ON k.id=j.property_type
  LEFT JOIN project_types n ON n.id=c.project_types
  LEFT JOIN project_types p ON p.id=a.is_mixed_use
  LEFT JOIN unitmodels f ON a.unitmodels=f.id
  LEFT JOIN areas g ON (b.areas=g.id && g.countries='1')
  LEFT JOIN areas_enquired h ON (d.id=h.enquiry_info && b.id=h.id)
  LEFT JOIN agents i ON d.agents=i.id 
  LEFT JOIN emirates s ON g.emirates=s.id
  LEFT JOIN tenant_info t ON (t.areas_enquired=a.areas_enquired && t.project_types=n.id ) ORDER BY a.xmllastupdated DESC

Kindly help, thanks in advance..