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?
Tomas Helgi JohannssonConnect With a Mentor Commented:

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
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.