mysql - Optimise Quesry containing Joins and Sub Queries -

mysql - Optimise Quesry containing Joins and Sub Queries -

i've inherited application client , have been working on next query takes 187 seconds , searches 304m table rows in order send 444 result rows.

am right want rid of sub selects , replace them joins? can't find way correctly. help optimising query hugely appreciated. thanks...

select business.name, business.primary_city, count(click.id) clicks, count(distinct email_leads.parent_message_id) tot_email_leads, count(bc.business_id) county_no, count(br.business_id) region_no, count(reveals.id) reveals_no businesses business left bring together business_clickthroughs click on ( business.id = click.business_id , (click.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59')) left bring together users u on business.id = u.business_id left bring together messages email_leads on (u.id = email_leads.from_to , (email_leads.parent_message_id null or email_leads.parent_message_id = email_leads.id ) , (email_leads.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59')) left bring together business_counties bc on business.id = bc.business_id left bring together businesses_business_types bt on business.id = bt.business_id left bring together business_reveals reveals on (reveals.business_id = business.id , (reveals.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59')) left bring together business_regions br on business.id = br.business_id 1=1 grouping business.id;

judging query writing, subselects right way go. don't show actual original query, speculation.

your query joining info along separate dimensions -- users, messages, counties, "reveals" (whatever is). joins result in cartesian product of these dimensions each business, farther magnifying info , slowing query down.

and, if larger tables have 300+ 1000000 rows, 300 seconds summarize all info in 8 or tables doesn't seem unreasonable. query doesn't have where conditions filtering data. joins left joins, reducing filtering.

if performance issue, inquire another question. include actual query, explain plan query, , layouts of tables (particularly index structures).

mysql sql join subquery