Tuning cannot fix high CPU. Redesigning SELECTs/indexes/schema can. But I don't have enough information to work with. You found the naughty query, now I need to see (as previously requested)
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]
select a.id, call_category,logged_date, a.call_id,
customer_name, phone, mobile, account_number,
address, a.area, city, complaint,
process_code, agent_reply, agent_comments,
fstatus, cstatus, assignto, sub_process_code,
cleared_date, closed_date, cleared_datetime,
closed_datetime, cust_type, node_name,
max(c.iqara_id) as iqara_id,
count(distinct b.history_id) as repeatcalls,
modem_status, soc_desc, action_taken,
rem_desc,
ifnull(reopened,0) as reopened,
CASE
WHEN fstatus = 'Closed' THEN 0
WHEN cstatus = 'Pending' THEN TIMESTAMPDIFF(MINUTE,
a.logged_date,now())/60
WHEN cstatus = 'Cleared' THEN TIMESTAMPDIFF(MINUTE,
a.logged_date,a.cleared_datetime)/60
WHEN cstatus = 'Closed' THEN TIMESTAMPDIFF(MINUTE,
a.logged_date,a.closed_datetime)/60
END as open_hrs,
CASE
WHEN fstatus = 'Closed' THEN 0
WHEN cstatus = 'Pending' THEN TIMESTAMPDIFF(MINUTE,
a.logged_date,now())%60
WHEN cstatus = 'Cleared' THEN TIMESTAMPDIFF(MINUTE,
a.logged_date,a.cleared_datetime)%60
WHEN cstatus = 'Closed' THEN TIMESTAMPDIFF(MINUTE,
a.logged_date,a.closed_datetime)%60
END as open_min
from new_call a
left join history b on a.id=b.id
left join history_iqara c on a.id=c.id
left join modem d on d.cust_acct_id=cast(a.account_number as SIGNED)
left join userdetail e on a.account_number = e.account_no
left join mast_area f on f.cust_acct_id =
cast(a.account_number as SIGNED)
left join compsource_m g on a.comp_source = g.soc_id
left join co_ordinator_remarks_m h on c.rem_no = h.rem_no
left join call_promo i on a.call_id = i.call_id
left join subprocesscode_m j on a.sub_process_code = j.spc
left join master_call_category k on j.mast_call_category_id = k.id
where city = 'Vadodara'
and process_code = 3
and sub_process_code in ('3.a','3.b',
'3.c','3.d','3.f','3.g','3.e','3.h',
'3.i')
and fdate >= '2009-03-01'
and fdate <= '2009-05-19'
group by a.id,k.call_category, a.call_id,
customer_name, phone, mobile, account_number,
address, a.area, city, complaint,
process_code, agent_reply, agent_comments,
fstatus, cstatus, assignto, sub_process_code,
cleared_date, closed_date, cleared_datetime,
closed_datetime, logged_date, cust_type,
node_name , modem_status, soc_desc,
iqara_id, action_taken, rem_desc,
reopened
having c.iqara_id =
( SELECT max(iqara_id)
from history_iqara
where id = a.id )
or c.iqara_id is null
order by logged_date desc;
"cast(a.account_number as SIGNED)" -- cannot use index on account_number
This is a strange pattern...
SELECT ... max(c.iqara_id) ...
GROUP BY -- not iquara_id
HAVING c.iqara_id ...
because c.iquara_id is not available by the time HAVING is executed.
Are you looking only at the rows with the max value of c_iqara_id, plus any null ones? If so, consider this pattern:
SELECT ...
FROM ...
LEFT JOIN ( SELECT ... ) c ON ...
LEFT JOIN ...
The idea is to find the few rows that match the restrictions on history_iqara, before having to haul around lots of data. Note how it had to hit 406K rows (among the several tables) during the query.
It may be useful to rollup b, too: count(distinct b.history_id) as repeatcalls
Be sure to do the relevant parts of the GROUP BY when doing the above suggestions.
I can't tell which fields are which in the GROUP BY.
I suspect there are more hints available, but I can't see them without seeing the EXPLAIN and TABLE STATUS and CREATE TABLEs.