In my application we have a query to fetch records from 4 tables. The below is the query
SELECT a.DeviceId, cpu.Time as Field_6, a.DeviceHostName as Field_5, avg(cpu.usagePercentage) as Field_1, avg(mem.usagePercentage) as Field_2, avg(disk.read_kb_per_sec)/1024 as Field_3, avg(net.BytesInPerSec)/1024 as Field_4 FROM devices b inner join vmware_relationships vmr on vmr.fromid = b.deviceid and type = 3 inner join devices a on a.deviceid = b.deviceid and a.retire = 0 left join (select deviceid,time,avg(usagePercentage) as usagePercentage from vmware_sum_cpu_perf_stats where summaryunit = 'hourly' group by deviceid,time) cpu on vmr.toid = cpu.deviceid left join (select deviceid,time,avg(usagePercentage) as usagePercentage from vmware_sum_mem_perf_stats where summaryunit = 'hourly' group by deviceid,time) mem on vmr.toid = mem.deviceid and cpu.time = mem.time left join (select deviceid,time,avg(diskUsage_in_kbps) as read_kb_per_sec from vmware_sum_disk_perf_stats where summaryunit = 'hourly' group by deviceid,time) disk on vmr.toid = disk.deviceid and cpu.time = disk.time left join (select deviceid,time,avg(netUsage_in_kbps) as BytesInPerSec from ent_host_perf_net_sum where summaryunit = 'hourly' group by deviceid,time) net on vmr.toid = net.deviceid and cpu.time = net.time WHERE a.DeviceId IN (42,34,69,53,14,16,15,17,52,55,51,59,54,57,12,22,1 3,92,87,88 ,8,6,9,10,26,44,28,43,2,29,45,30,46,11,7,90,72,83, 91,84,80,8 1,86,85,89,5,82,93,94,95,21,25,48,66,120,1,40,63,1 00,18,47,1 9,49,50,58,41,111,115,116,75,99,74,76,67,61,101,71 ,68,105,79 ,78,96,102,33,37,38,27,39,31,35,32,36,110,114,109, 106,108,11 3,107,77,112,103,70,62,104,60,98,65,56,64,97,20,24 ,23,3,4,73 ) AND cpu.Time >= 1344252000000 AND cpu.Time <= 1345548000000 GROUP BY b.deviceid,cpu.time,mem.time,disk.time,net.time
And the following are the Create table queries for all the tables,
CREATE TABLE `vmware_sum_disk_perf_stats` (
`Time` bigint(20) NOT NULL DEFAULT '0',
`deviceId` int(20) NOT NULL DEFAULT '0',
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`Id` varchar(50) NOT NULL DEFAULT '',
`numberRead` float DEFAULT NULL,
`numberWrite` float DEFAULT NULL,
`read_kb_per_sec` float DEFAULT NULL,
`write_kb_per_sec` float DEFAULT NULL,
`diskUsage_in_kbps` float DEFAULT NULL,
`numberReadWrite` float DEFAULT NULL,
`deviceLatency` bigint(20) DEFAULT NULL,
`readLatency` bigint(20) DEFAULT NULL,
`writeLatency` bigint(20) DEFAULT NULL,
`queueLatency` bigint(20) DEFAULT NULL,
PRIMARY KEY (`deviceId`,`Time`,`summaryUnit`,`Id`),
KEY `index2` (`deviceId`,`Id`,`summaryUnit`,`Time`),
KEY `SummaryUnit` (`summaryUnit`,`Time`),
KEY `index` (`summaryUnit`,`deviceId`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `vmware_sum_cpu_perf_stats` (
`Time` bigint(20) NOT NULL DEFAULT '0',
`deviceId` int(20) NOT NULL DEFAULT '0',
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`usagePercentage` float DEFAULT NULL,
`usageMhz` float DEFAULT NULL,
`used` float DEFAULT NULL,
`guaranteed` float DEFAULT NULL,
`extra` float DEFAULT NULL,
`ready` float DEFAULT NULL,
`wait` float DEFAULT NULL,
`sys` float DEFAULT NULL,
`idle` float DEFAULT NULL,
PRIMARY KEY (`deviceId`,`Time`,`summaryUnit`),
KEY `time` (`deviceId`,`summaryUnit`,`Time`),
KEY `index` (`summaryUnit`,`deviceId`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `vmware_sum_mem_perf_stats` (
`Time` bigint(20) NOT NULL DEFAULT '0',
`deviceId` int(20) NOT NULL DEFAULT '0',
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`usagePercentage` float DEFAULT '0',
`consumed` float DEFAULT '0',
`zero` float DEFAULT '0',
`state` int(20) DEFAULT '0',
`active` float DEFAULT '0',
`shared` float DEFAULT '0',
`granted` float DEFAULT '0',
`overhead` float DEFAULT '0',
`swapin` float DEFAULT '0',
`swapped` float DEFAULT '0',
`swapOut` float DEFAULT '0',
`swapUsed` float DEFAULT '0',
`swapTarget` float DEFAULT '0',
`unreserved` float DEFAULT '0',
`vmmemctl` float DEFAULT '0',
`vmmemctlTarget` float DEFAULT '0',
`heapFree` float DEFAULT '0',
`heap` float DEFAULT '0',
`reservedCapacity` float DEFAULT '0',
PRIMARY KEY (`deviceId`,`Time`,`summaryUnit`),
KEY `index2` (`Time`,`summaryUnit`,`deviceId`),
KEY `SummaryUnit` (`summaryUnit`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `ent_host_perf_net_sum` (
`DeviceId` int(11) NOT NULL,
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`Time` bigint(20) NOT NULL DEFAULT '0',
`GmtOffset` bigint(20) NOT NULL DEFAULT '0',
`Day` bigint(20) NOT NULL DEFAULT '0',
`Hour` int(11) NOT NULL DEFAULT '0',
`InterfaceId` varchar(50) NOT NULL,
`NoECBAvailCnt` float DEFAULT NULL,
`BytesInPerSec` float DEFAULT NULL,
`BytesOutPerSec` float DEFAULT NULL,
`PacketsCollisionsPerSec` float DEFAULT NULL,
`PacketsInPerSec` float DEFAULT NULL,
`PacketsInErrorsPerSec` float DEFAULT NULL,
`PacketsInDiscardedPerSec` float DEFAULT NULL,
`PacketsInUnicastPerSec` float DEFAULT NULL,
`PacketsInNonunicastPerSec` float DEFAULT NULL,
`PacketsInCompressedPerSec` float DEFAULT NULL,
`PacketsInDroppedPerSec` float DEFAULT NULL,
`PacketsInUnknownPerSec` float DEFAULT NULL,
`PacketsInFrameAlignmentErrorsPerSec` float DEFAULT NULL,
`PacketsInFifoOverrunErrorsPerSec` float DEFAULT NULL,
`PacketsOutPerSec` float DEFAULT NULL,
`PacketsOutErrorsPerSec` float DEFAULT NULL,
`PacketsOutDiscardedPerSec` float DEFAULT NULL,
`PacketsOutUnicastPerSec` float DEFAULT NULL,
`PacketsOutNonunicastPerSec` float DEFAULT NULL,
`PacketsOutCompressedPerSec` float DEFAULT NULL,
`PacketsOutDroppedPerSec` float DEFAULT NULL,
`PacketsOutCarrierErrorsPerSec` float DEFAULT NULL,
`netUsage_in_kbps` float DEFAULT NULL,
PRIMARY KEY (`DeviceId`,`summaryUnit`,`Time`,`InterfaceId`),
KEY `index2` (`summaryUnit`,`DeviceId`,`InterfaceId`,`Time`),
KEY `Time` (`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Also I have attached the explain query.
Can someone give some suggestions to improve the performance on this query?
SELECT a.DeviceId, cpu.Time as Field_6, a.DeviceHostName as Field_5, avg(cpu.usagePercentage) as Field_1, avg(mem.usagePercentage) as Field_2, avg(disk.read_kb_per_sec)/1024 as Field_3, avg(net.BytesInPerSec)/1024 as Field_4 FROM devices b inner join vmware_relationships vmr on vmr.fromid = b.deviceid and type = 3 inner join devices a on a.deviceid = b.deviceid and a.retire = 0 left join (select deviceid,time,avg(usagePercentage) as usagePercentage from vmware_sum_cpu_perf_stats where summaryunit = 'hourly' group by deviceid,time) cpu on vmr.toid = cpu.deviceid left join (select deviceid,time,avg(usagePercentage) as usagePercentage from vmware_sum_mem_perf_stats where summaryunit = 'hourly' group by deviceid,time) mem on vmr.toid = mem.deviceid and cpu.time = mem.time left join (select deviceid,time,avg(diskUsage_in_kbps) as read_kb_per_sec from vmware_sum_disk_perf_stats where summaryunit = 'hourly' group by deviceid,time) disk on vmr.toid = disk.deviceid and cpu.time = disk.time left join (select deviceid,time,avg(netUsage_in_kbps) as BytesInPerSec from ent_host_perf_net_sum where summaryunit = 'hourly' group by deviceid,time) net on vmr.toid = net.deviceid and cpu.time = net.time WHERE a.DeviceId IN (42,34,69,53,14,16,15,17,52,55,51,59,54,57,12,22,1 3,92,87,88 ,8,6,9,10,26,44,28,43,2,29,45,30,46,11,7,90,72,83, 91,84,80,8 1,86,85,89,5,82,93,94,95,21,25,48,66,120,1,40,63,1 00,18,47,1 9,49,50,58,41,111,115,116,75,99,74,76,67,61,101,71 ,68,105,79 ,78,96,102,33,37,38,27,39,31,35,32,36,110,114,109, 106,108,11 3,107,77,112,103,70,62,104,60,98,65,56,64,97,20,24 ,23,3,4,73 ) AND cpu.Time >= 1344252000000 AND cpu.Time <= 1345548000000 GROUP BY b.deviceid,cpu.time,mem.time,disk.time,net.time
And the following are the Create table queries for all the tables,
CREATE TABLE `vmware_sum_disk_perf_stats` (
`Time` bigint(20) NOT NULL DEFAULT '0',
`deviceId` int(20) NOT NULL DEFAULT '0',
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`Id` varchar(50) NOT NULL DEFAULT '',
`numberRead` float DEFAULT NULL,
`numberWrite` float DEFAULT NULL,
`read_kb_per_sec` float DEFAULT NULL,
`write_kb_per_sec` float DEFAULT NULL,
`diskUsage_in_kbps` float DEFAULT NULL,
`numberReadWrite` float DEFAULT NULL,
`deviceLatency` bigint(20) DEFAULT NULL,
`readLatency` bigint(20) DEFAULT NULL,
`writeLatency` bigint(20) DEFAULT NULL,
`queueLatency` bigint(20) DEFAULT NULL,
PRIMARY KEY (`deviceId`,`Time`,`summaryUnit`,`Id`),
KEY `index2` (`deviceId`,`Id`,`summaryUnit`,`Time`),
KEY `SummaryUnit` (`summaryUnit`,`Time`),
KEY `index` (`summaryUnit`,`deviceId`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `vmware_sum_cpu_perf_stats` (
`Time` bigint(20) NOT NULL DEFAULT '0',
`deviceId` int(20) NOT NULL DEFAULT '0',
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`usagePercentage` float DEFAULT NULL,
`usageMhz` float DEFAULT NULL,
`used` float DEFAULT NULL,
`guaranteed` float DEFAULT NULL,
`extra` float DEFAULT NULL,
`ready` float DEFAULT NULL,
`wait` float DEFAULT NULL,
`sys` float DEFAULT NULL,
`idle` float DEFAULT NULL,
PRIMARY KEY (`deviceId`,`Time`,`summaryUnit`),
KEY `time` (`deviceId`,`summaryUnit`,`Time`),
KEY `index` (`summaryUnit`,`deviceId`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `vmware_sum_mem_perf_stats` (
`Time` bigint(20) NOT NULL DEFAULT '0',
`deviceId` int(20) NOT NULL DEFAULT '0',
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`usagePercentage` float DEFAULT '0',
`consumed` float DEFAULT '0',
`zero` float DEFAULT '0',
`state` int(20) DEFAULT '0',
`active` float DEFAULT '0',
`shared` float DEFAULT '0',
`granted` float DEFAULT '0',
`overhead` float DEFAULT '0',
`swapin` float DEFAULT '0',
`swapped` float DEFAULT '0',
`swapOut` float DEFAULT '0',
`swapUsed` float DEFAULT '0',
`swapTarget` float DEFAULT '0',
`unreserved` float DEFAULT '0',
`vmmemctl` float DEFAULT '0',
`vmmemctlTarget` float DEFAULT '0',
`heapFree` float DEFAULT '0',
`heap` float DEFAULT '0',
`reservedCapacity` float DEFAULT '0',
PRIMARY KEY (`deviceId`,`Time`,`summaryUnit`),
KEY `index2` (`Time`,`summaryUnit`,`deviceId`),
KEY `SummaryUnit` (`summaryUnit`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `ent_host_perf_net_sum` (
`DeviceId` int(11) NOT NULL,
`summaryUnit` enum('Hourly','Daily','Weekly','Monthly') NOT NULL DEFAULT 'Hourly',
`Time` bigint(20) NOT NULL DEFAULT '0',
`GmtOffset` bigint(20) NOT NULL DEFAULT '0',
`Day` bigint(20) NOT NULL DEFAULT '0',
`Hour` int(11) NOT NULL DEFAULT '0',
`InterfaceId` varchar(50) NOT NULL,
`NoECBAvailCnt` float DEFAULT NULL,
`BytesInPerSec` float DEFAULT NULL,
`BytesOutPerSec` float DEFAULT NULL,
`PacketsCollisionsPerSec` float DEFAULT NULL,
`PacketsInPerSec` float DEFAULT NULL,
`PacketsInErrorsPerSec` float DEFAULT NULL,
`PacketsInDiscardedPerSec` float DEFAULT NULL,
`PacketsInUnicastPerSec` float DEFAULT NULL,
`PacketsInNonunicastPerSec` float DEFAULT NULL,
`PacketsInCompressedPerSec` float DEFAULT NULL,
`PacketsInDroppedPerSec` float DEFAULT NULL,
`PacketsInUnknownPerSec` float DEFAULT NULL,
`PacketsInFrameAlignmentErrorsPerSec` float DEFAULT NULL,
`PacketsInFifoOverrunErrorsPerSec` float DEFAULT NULL,
`PacketsOutPerSec` float DEFAULT NULL,
`PacketsOutErrorsPerSec` float DEFAULT NULL,
`PacketsOutDiscardedPerSec` float DEFAULT NULL,
`PacketsOutUnicastPerSec` float DEFAULT NULL,
`PacketsOutNonunicastPerSec` float DEFAULT NULL,
`PacketsOutCompressedPerSec` float DEFAULT NULL,
`PacketsOutDroppedPerSec` float DEFAULT NULL,
`PacketsOutCarrierErrorsPerSec` float DEFAULT NULL,
`netUsage_in_kbps` float DEFAULT NULL,
PRIMARY KEY (`DeviceId`,`summaryUnit`,`Time`,`InterfaceId`),
KEY `index2` (`summaryUnit`,`DeviceId`,`InterfaceId`,`Time`),
KEY `Time` (`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Also I have attached the explain query.
Can someone give some suggestions to improve the performance on this query?
Comment