Query Taking 2.5hrs on 5.6.20 version
please can any one help to tune this below query it taking 2 hrs 30 min on new installed mysql-5.6.20
mysql> explain select s.id,s.service_name,s.description,s.lob,s.subscriber_class,s.cp_id,s.startdate,s.enddate,s.network_prov,s.channel,s.service_list_name,s.act_retry_frequency,s.act_retry_days,s.activation_optin,s.service_usage_type,s.service_usage_type_value,s.owner_emailid,s.renewal_optin,s.allowed_circles,s.type,s.act_retry_time,s.msgid,s.username,s.svc_creation_time,s.combo_services,s.msisdn,s.category,s.sub_category,s.search_tag,s.ivsm,s.uup_lob,s.adj_appcode,s.cp_code,s.svc_code,s.group_id,s.parentid,s.childid,s.is_pretopost,s.is_posttopre,s.is_whitelist,s.network_id,s.genre,s.sub_genre,s.sub_sub_genre,s.svc_updation_time,sd.*,gen.name genre_name, subGen.name sub_genre_name, subSubGen.name sub_sub_genre_name ,s.service_name 'keywords',ifnull(group_concat(concat(t.token,'#',t.validation_url,'#',t.validate_by)),'') 'tokens (token#validation_url#validate_by)', ifnull(group_concat(distinct act.price),'') activation_price, ifnull(group_concat(distinct act1.validity),'') activation_validity, ifnull(group_concat(distinct ren.price),'') renewal_price, ifnull(group_concat(distinct ren1.validity),'') renewal_validity, ifnull(group_concat(concat(ec.event_name,'#',ec.event_price)),'') 'event_charging (event_name#event_price)', ifnull(group_concat(concat(tu.topup_prefix,'#',tu.topup_key,'#',tu.topup_price,'#',tu.usage,'#',tu.validity)),'') 'topups (topup_prefix#topup_key#topup_price#usage#validity)', ifnull(group_concat(concat(te.event_name,'#',te.charging_model,'#',te.leave_balance,'#',te.charging_days_mode,'#',te.price_point,'#',te.act_failure_retry_days,'#',te.act_failure_retry_frequency,'#',te.retry_time,',&&')),'') 'trigger_event(event_name#charging_model#leave_balance#charging_days_mode#price_point#act_failure_retry_days#act_failure_retry_frequency#retry_time)',if(s.chargetagid='','NA',s.chargetagid) from se_service s join se_service_details sd on s.id=sd.serviceid left join se_price_plan act on sd.act_price_point=act.plan_name left join se_price_plan_rule act1 on act.id=act1.plan_id left join se_price_plan ren on sd.renewal_price_point=ren.plan_name left join se_price_plan_rule ren1 on ren.id=ren1.plan_id left join se_event_charging ec on sd.shortcode_keyword=ec.event_id and ec.event_name<>'' left join se_tokens t on sd.shortcode_keyword=t.name and t.token<>'' left join se_topup tu on sd.shortcode_keyword=tu.name and tu.topup_key<>'' left join se_trigger_event te on sd.shortcode_keyword=te.name and te.event_name<>'' join se_genre gen on s.genre=gen.id join se_genre subGen on s.sub_genre=subGen.id join se_genre subSubGen on s.sub_sub_genre=subSubGen.id group by s.id,sd.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 6809
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: sd
type: ref
possible_keys: se_detail_cidx
key: se_detail_cidx
key_len: 4
ref: jmailer_ibmse.s.id
rows: 1
Extra: NULL
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: gen
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: jmailer_ibmse.s.genre
rows: 1
Extra: Using index condition
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: subGen
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: jmailer_ibmse.s.sub_genre
rows: 1
Extra: Using index condition
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: subSubGen
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: jmailer_ibmse.s.sub_sub_genre
rows: 1
Extra: Using index condition
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: act
type: ref
possible_keys: plan_name_idx,name_dur_idx
key: plan_name_idx
key_len: 153
ref: jmailer_ibmse.sd.act_price_point
rows: 2
Extra: NULL
*************************** 7. row ***************************
id: 1
select_type: SIMPLE
table: act1
type: ALL
possible_keys: plan_id_idx
key: NULL
key_len: NULL
ref: NULL
rows: 41182
Extra: Range checked for each record (index map: 0x2)
*************************** 8. row ***************************
id: 1
select_type: SIMPLE
table: ren
type: ref
possible_keys: plan_name_idx,name_dur_idx
key: plan_name_idx
key_len: 153
ref: jmailer_ibmse.sd.renewal_price_point
rows: 2
Extra: NULL
*************************** 9. row ***************************
id: 1
select_type: SIMPLE
table: ren1
type: ALL
possible_keys: plan_id_idx
key: NULL
key_len: NULL
ref: NULL
rows: 41182
Extra: Range checked for each record (index map: 0x2)
*************************** 10. row ***************************
id: 1
select_type: SIMPLE
table: ec
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10476
Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 11. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10308
Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 12. row ***************************
id: 1
select_type: SIMPLE
table: tu
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5966
Extra: Using where; Using join buffer (Block Nested Loop)
*************************** 13. row ***************************
id: 1
select_type: SIMPLE
table: te
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 112
Extra: Using where; Using join buffer (Block Nested Loop)
13 rows in set (0.02 sec)