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..