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.