optimize mysql statement


How can I optimize this MySQL statement ?

select  g.gmc1_id, g.gmc2_id, g.gmc3_id, g.gmc4_id, g.lb_gmc4, s.subfamily_id, s.lb_subfamily , f.factor_id, f.lb_factor_fre, f.lb_factor_dut, i.*, pp2013.sales_test as 'test2013', pp2012.sales_test as 'test 2012'
, test.test, test.break1qty, test.break1percent, test.break1test, test.break2qty, test.break2percent, test.break2test, test.last_editor_username, test.locked
FROM t_item i
join t_itemgroup ig on i.itemgroup_id = ig.itemgroup_id
join t_factor f on ig.factor_id = f.factor_id
join t_subfamily s on f.subfamily_id = s.subfamily_id
join t_gmc g on s.gmc4_id = g.gmc4_id
left join t_ext_previous_tests pp2013 on i.testdb_code = pp2013.testdb_code and i.item_id = pp2013.step_id and pp2013.`year` = '2013'
left join t_ext_previous_tests pp2012 on i.testdb_code = pp2012.testdb_code and i.item_id = pp2012.step_id and pp2012.`year` = '2012'
left join t_tests test on test.step_id = i.item_id and test.testdb_code = i.testdb_code

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
Please post the EXPLAIN plan of the query.
Usually you will see missing indexes.
Amar BardoliwalaCommented:
Hello bibi92,

Normally indexes do the job for you and sometime you can also create view and use view to display data.

Apart from that look at following links that will help you.



Thank you.

Amar Bardoliwala
Tomas Helgi JohannssonCommented:

Indexes like these on the tables will sertainly help

Create index ix_itemgroup_1 on t_itemgroup (itemgroup_id asc, factor_id asc)
Create index ix_factorid_1 on t_factor(factor_id asc,subfamily_id asc)
Create index ix_subfamily_1 on t_subfamily (subfamily_id asc, gmc4_id asc)

etc. All columns in the join and where clauses and together (not one column/index).

    Tomas Helgi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.