help me tune the query

[prakash gr]

Hi,

I need to tune the following query, please do help me.


select a.id , call_id, logged_date, account_number, process_code, sub_process_code, customer_name, cust_type,
count(distinct b.history_id) as repeatcalls, ifnull(d.cust_address,a.address)as cust_address, a.area, city,
node_name, soc_desc,soc_id, complaint, agent_reply, agent_comments, phone, mobile, assignto, cstatus,lco_name,
max(e.iqara_id) as iqara_id, action_taken, rem_desc, CASE WHEN cstatus = 'Pending' THEN
TIMESTAMPDIFF(MINUTE,a.logged_date,now()) WHEN cstatus = 'Cleared' THEN
TIMESTAMPDIFF(MINUTE,a.logged_date,a.cleared_datetime) END as ageing_mins from
new_call a left join history b on a.id = b.id left join modem c on a.account_number = c.cust_acct_id
left join userdetail d on a.account_number = d.account_no left join history_iqara e on a.id=e.id left join
compsource_m f on a.comp_source = f.soc_id left join co_ordinator_remarks_m g on e.rem_no = g.rem_no where city in
('Bangalore') and process_code in (3) and lower(cstatus)='Pending' group by a.id, call_id, logged_date, account_number,
process_code, sub_process_code, customer_name, cust_type, ifnull(d.cust_address,a.address), a.area, city, node_name,
soc_desc,soc_id, complaint, agent_reply, agent_comments, phone, mobile, assignto,lco_name,iqara_id, action_taken,
rem_desc, cstatus, cleared_datetime having e.iqara_id = (select max(iqara_id) from history_iqara where id = a.id) or
(e.iqara_id is null) order by logged_date desc


explain plan:

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3186623
filtered: 100.00
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: b
type: ref
possible_keys: IX_HISTORY1,IX_ID
key: IX_HISTORY1
key_len: 9
ref: agentlog.a.id
rows: 1
filtered: 100.00
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: c
type: ref
possible_keys: IDX_CUST_ACCT_ID
key: IDX_CUST_ACCT_ID
key_len: 4
ref: agentlog.a.account_number
rows: 1
filtered: 100.00
Extra:
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: d
type: ref
possible_keys: IDX_Account_NO
key: IDX_Account_NO
key_len: 453
ref: agentlog.a.account_number
rows: 1
filtered: 100.00
Extra:
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: e
type: ref
possible_keys: idx_id_logdate,history_iqara14,IX_ID
key: idx_id_logdate
key_len: 9
ref: agentlog.a.id
rows: 1
filtered: 100.00
Extra:
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: f
type: eq_ref
possible_keys: IX_SOCID
key: IX_SOCID
key_len: 4
ref: agentlog.a.comp_source
rows: 1
filtered: 100.00
Extra:
*************************** 7. row ***************************
id: 1
select_type: PRIMARY
table: g
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: agentlog.e.rem_no
rows: 1
filtered: 100.00
Extra:
*************************** 8. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: history_iqara
type: ref
possible_keys: idx_id_logdate,history_iqara14,IX_ID
key: idx_id_logdate
key_len: 9
ref: func
rows: 1
filtered: 100.00
Extra: Using where; Using index
8 rows in set, 4 warnings (0.00 sec)


Thanks

Prakash GR