Optimisation of query

Question

javedsai 0 Light Poster

13 Years Ago

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

database-design mysql

2 Contributors
7 Replies
188 Views
3 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by javedsai

Dani AI

Generated 6 Months Ago

Short summary and next steps based on the thread: the query is scanning property_info and then touching many LEFT JOINs, so MySQL ends up doing a large intermediate result plus a filesort/temporary for the ORDER BY. is correct to suggest indexing the join/filter columns; ’s attempt to move WHERE conditions into the LEFT JOIN ON changed row counts because the join semantics changed. The immediate goal is (1) make the planner start from a small, selective set and (2) give it indexes it can actually use.

Why your row count changed when you moved filters: a WHERE condition referencing a LEFT-joined table filters rows after the join (which effectively turns that LEFT JOIN into an INNER JOIN). Moving the same condition into the ON keeps non-matching “left” rows. Decide which behavior you want: if matching is required, use INNER JOIN; if you must keep unmatched rows, keep LEFT JOIN and put the predicate in ON.

Practical indexing suggestions (test each change with EXPLAIN and on staging):

CREATE INDEX idx_projects_project_types ON projects(project_types);
CREATE INDEX idx_areas_countries ON areas(countries);
CREATE INDEX idx_enquiry_customer_types ON enquiry_info(customer_types);
CREATE INDEX idx_prop_projects ON property_info(projects);
CREATE INDEX idx_proptype_models_projects_unitmodels ON proptype_models(projects, unitmodels);
CREATE INDEX idx_tenant_areas_projtypes ON tenant_info(areas_enquired, project_types);
CREATE INDEX idx_prop_xmllastupdated ON property_info(xmllastupdated);

Composite indexes are important where you join on two columns together (proptype_models and tenant_info above). Index order matters; try both orders if selectivity is unclear.

Query-shape strategies: avoid SELECT *; fetch only needed columns. If you usually show “recent” rows by xmllastupdated, first select the small set of property_info IDs ordered by xmllastupdated (LIMIT), then join the other tables to that small set (temporary table or derived subquery). Convert LEFT JOINs to INNER JOINs when a matching row is required. After each change, run EXPLAIN, measure elapsed time, and balance index count against write/update overhead.

All 7 Replies

pritaeas 2,276 Most Valuable Poster

13 Years Ago

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

pritaeas 2,276 Most Valuable Poster

13 Years Ago

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.

Edited 13 Years Ago by pritaeas

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

javedsai 0 Light Poster · Answer 1 · 2012-12-10T09:16:38+00:00

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 0 Light Poster · Answer 2 · 2012-12-10T09:22:18+00:00

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

javedsai 0 Light Poster · Answer 3 · 2012-12-10T09:40:03+00:00

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 2,276 Most Valuable Poster Moderator Featured Poster · Answer 4 · 2012-12-10T10:12:53+00:00

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 0 Light Poster · Answer 5 · 2012-12-10T12:08:46+00:00

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

Optimisation of query

Dani AI

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers