MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query Taking 2.5hrs on 5.6.20 version
Posted by: Rick James
Date: March 04, 2015 08:18PM

You have
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
It looks like act1, ren1, and ec do not have indexes that are needed -- namely on plan_id, plan_id, and event_id, respectively.

Asp Peter say, get rid of LEFT unless you deliberately need it -- as in the 'right' table may not have a matching row, and you 'want' NULLs.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query Taking 2.5hrs on 5.6.20 version
1100
March 04, 2015 08:18PM


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.