Hi, I want to optimize the below query , innodb is currently being used , it takes 13 minute execution time , kindly help .
query:

select q.host_name, q.service_name, q.kpi_name , MAX(a0) as 17_Aug_15, MAX(a1) as 31_Aug_15, Min(h) as Min, MAX(i) as Max , AVG(i) as Average from (select p.host_name, p.service_name, p.kpi_name , CASE when DATE(from_unixtime(p.timestamp))='2015-10-17' then p.Average ELSE 0 END as a0 , CASE when DATE(from_unixtime(p.timestamp))='2015-10-20' then p.Average ELSE 0 END as a1 , p.Average as h, p.Average as i from ( select oam_db_perf.System_CpuUsage_Linux.timestamp, oam_db_perf.System_CpuUsage_Linux.host_name, case when 1=1 then 'CPU' end as service_name, oam_db_perf.System_CpuUsage_Linux.kpi_name, ROUND(AVG(oam_db_perf.System_CpuUsage_Linux.percentage_value),2) as Average, MAX(oam_db_perf.System_CpuUsage_Linux.percentage_value*1) as Peak from oam_db_perf.System_CpuUsage_Linux where timestamp between 1442687400 and 1445279400 group by oam_db_perf.System_CpuUsage_Linux.host_name, oam_db_perf.System_CpuUsage_Linux.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_CpuUsage_Windows.timestamp, oam_db_perf.System_CpuUsage_Windows.host_name, case when 1=1 then 'CPU' end as service_name, case when 1=1 then 'CPU_Usage' end as kpi_name, ROUND(AVG(oam_db_perf.System_CpuUsage_Windows.percentage_value),2) as Average, MAX(oam_db_perf.System_CpuUsage_Windows.percentage_value*1) as Peak from oam_db_perf.System_CpuUsage_Windows where timestamp between 1442687400 and 1445279400 and oam_db_perf.System_CpuUsage_Windows.kpi_name='5m' group by oam_db_perf.System_CpuUsage_Windows.host_name, oam_db_perf.System_CpuUsage_Windows.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_MemAvalaible_Linux.timestamp, oam_db_perf.System_MemAvalaible_Linux.host_name, case when 1=1 then 'Memory' end as service_name, case when 1=1 then 'Memory %' end as kpi_name, (ROUND(AVG(100 - oam_db_perf.System_MemAvalaible_Linux.percentage_value),2)) as Average, (MAX(100 - oam_db_perf.System_MemAvalaible_Linux.percentage_value*1)) as Peak from oam_db_perf.System_MemAvalaible_Linux where oam_db_perf.System_MemAvalaible_Linux.kpi_name like '%Memory_Free%' and timestamp between 1442687400 and 1445279400 group by oam_db_perf.System_MemAvalaible_Linux.host_name, oam_db_perf.System_MemAvalaible_Linux.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_MemAvalaible_Windows.timestamp, oam_db_perf.System_MemAvalaible_Windows.host_name, case when 1=1 then 'Memory' end as service_name, oam_db_perf.System_MemAvalaible_Windows.kpi_name, ROUND(AVG(oam_db_perf.System_MemAvalaible_Windows.percentage_value),2) as Average, MAX(oam_db_perf.System_MemAvalaible_Windows.percentage_value*1) as Peak from oam_db_perf.System_MemAvalaible_Windows where timestamp between 1442687400 and 1445279400 and oam_db_perf.System_MemAvalaible_Windows.kpi_name like '%physical memory %%' group by oam_db_perf.System_MemAvalaible_Windows.host_name, oam_db_perf.System_MemAvalaible_Windows.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_SwapAvalaible_Linux.timestamp, oam_db_perf.System_SwapAvalaible_Linux.host_name, case when 1=1 then 'Swap' end as service_name, case when 1=1 then 'Swap %' end as kpi_name, (ROUND(AVG(100 - oam_db_perf.System_SwapAvalaible_Linux.percentage_value),2)) as Average, (MAX(100 - oam_db_perf.System_SwapAvalaible_Linux.percentage_value*1)) as Peak from oam_db_perf.System_SwapAvalaible_Linux where oam_db_perf.System_SwapAvalaible_Linux.kpi_name like '%Swap_Free%' and timestamp between 1442687400 and 1445279400 group by oam_db_perf.System_SwapAvalaible_Linux.host_name, oam_db_perf.System_SwapAvalaible_Linux.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.Disk_Usage_Linux.timestamp, oam_db_perf.Disk_Usage_Linux.host_name, case when 1=1 then 'Disk' end as service_name, oam_db_perf.Disk_Usage_Linux.kpi_name, ROUND(AVG(oam_db_perf.Disk_Usage_Linux.percentage_value),2) as Average, MAX(oam_db_perf.Disk_Usage_Linux.percentage_value*1) as Peak from oam_db_perf.Disk_Usage_Linux where timestamp between 1442687400 and 1445279400 group by oam_db_perf.Disk_Usage_Linux.host_name, oam_db_perf.Disk_Usage_Linux.kpi_name , substring(from_unixtime(timestamp), 1, 7 ) UNION select oam_db_perf.System_Volumes_Windows.timestamp, oam_db_perf.System_Volumes_Windows.host_name, case when 1=1 then 'Disk' end as service_name, oam_db_perf.System_Volumes_Windows.kpi_name, (ROUND(AVG(100 - oam_db_perf.System_Volumes_Windows.percentage_value),2)) as Average, (MAX(100 - oam_db_perf.System_Volumes_Windows.percentage_value*1)) as Peak from oam_db_perf.System_Volumes_Windows where timestamp between 1442687400 and 1445279400 and oam_db_perf.System_Volumes_Windows.kpi_name like '%% %' and oam_db_perf.System_Volumes_Windows.kpi_name!='System Reserved' group by oam_db_perf.System_Volumes_Windows.host_name, oam_db_perf.System_Volumes_Windows.kpi_name , substring(from_unixtime(timestamp), 1, 7 )) as p where p.host_name in (select node_name from oam_db.node where cluster_name='AIRTEL_TANZANIA_MOBIQUITY' ) order by p.host_name,p.service_name , p.timestamp) as q group by q.host_name, q.service_name, q.kpi_name;

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.