MySQL Forums
Forum List  »  Optimizer & Parser

Query Taking 2.5hrs on 5.6.20 version
Posted by: ch srinivas
Date: March 04, 2015 01:30AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Query Taking 2.5hrs on 5.6.20 version
2526
March 04, 2015 01:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.