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,13,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,100,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?