query optimization
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;