Performance problem when table gets bigger
Posted by:
ICI MASA
Date: July 11, 2005 08:17AM
HI,
when I had 140.000 rows this query took 0.6 s or something, now I have over 3.000.000 rows and the query is upto 22 s, so there is probebly something that needs optemizing.
First the tables that I search in are as follows created:
____tbScanList_____
CREATE TABLE `tbScanList` (
`SID` int(10) NOT NULL auto_increment,
`DID` int(10) default NULL,
`TID` int(10) default NULL,
`Saddr` varchar(150) default NULL,
`Sinterval` int(10) default NULL,
`SnextScanTime` datetime default NULL,
`SqueryID` int(10) default NULL,
PRIMARY KEY (`SID`),
KEY `DID` (`DID`),
KEY `SID` (`SID`),
KEY `SqueryID` (`SqueryID`),
KEY `tbDevicetbScanList` (`DID`),
KEY `TID` (`TID`),
KEY `tbScanTemplatetbScanList` (`TID`),
CONSTRAINT `FK_tbScanList_1` FOREIGN KEY (`DID`) REFERENCES `tbDevice` (`DID`) ON DELETE CASCADE,
CONSTRAINT `FK_tbScanList_2` FOREIGN KEY (`TID`) REFERENCES `tbScanTemplate` (`TID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
_______tbPoint_______
CREATE TABLE `tbPoint` (
`NID` int(10) NOT NULL auto_increment,
`TID` int(10) NOT NULL default '0',
`Nname` varchar(50) default NULL,
`Ndesc` longtext,
`Ntype` int(10) default NULL,
`Nscale` int(10) default NULL,
`Noffset` int(10) default NULL,
`Nidx` int(10) default NULL,
`Nwritable` tinyint(1) unsigned NOT NULL default '0',
`Nunit` varchar(50) default NULL,
PRIMARY KEY (`NID`),
KEY `NID` (`NID`),
KEY `tbScanTemplatetbPoint` (`TID`),
KEY `TID` (`TID`),
CONSTRAINT `FK_tbPoint_1` FOREIGN KEY (`TID`) REFERENCES `tbScanTemplate` (`TID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
_______tbValue_______
CREATE TABLE `tbValue` (
`VID` int(10) NOT NULL auto_increment,
`DID` int(10) default NULL,
`NID` int(10) default NULL,
`Value` varchar(255) default NULL,
`VtimeStamp` datetime default NULL,
`Vaddr` int(10) default NULL,
PRIMARY KEY (`VID`),
KEY `DID` (`DID`),
KEY `NID` (`NID`),
KEY `tbDevicetbValue` (`DID`),
KEY `tbPointtbValue` (`NID`),
KEY `VID` (`VID`),
CONSTRAINT `FK_tbValue_1` FOREIGN KEY (`NID`) REFERENCES `tbPoint` (`NID`) ON DELETE CASCADE,
CONSTRAINT `FK_tbValue_2` FOREIGN KEY (`DID`) REFERENCES `tbDevice` (`DID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 10240 kB';
The Query looks as follows:
SELECT STRAIGHT_JOIN tbPoint.Nname, tbPoint.Nidx, tbPoint.NID, tbPoint.Nunit, tmpTb.Value, tmpTb.VtimeStamp
FROM tbScanList INNER JOIN tbPoint ON tbScanList.TID = tbPoint.TID INNER JOIN
(SELECT v1.DID, v1.NID, v1.Value, v1.VID, v1.VtimeStamp FROM tbValue v1 INNER JOIN
(SELECT NID, DID, max(VID) AS LastUpdate FROM tbValue GROUP BY NID, DID) v2
ON (v1.NID = v2.NID AND 42 = v2.DID AND v1.VID = v2.LastUpdate))
AS tmpTb ON tbPoint.NID = tmpTb.NID
WHERE tbScanList.DID = 42 ORDER BY tbPoint.Nname
When I run an Explain I get this Info:
+--+-----------+-----+----+-------------+---+-------+---+----+-----+
|ID|SELECT_TYPE| TABLE | TYPE | ROWS |EXTRA |
+--+-----------+-----+----+-------------+---+-------+---+----+-----+
|1 |PRIMARY | tbScanLinst | ref | 1 | Using where, Using temporary, Usin filesort
+--+-----------+-----+----+-------------+---+-------+---+----+-----+
|1 |PRIMARY | tbPoint | ref | 4 |
+--+-----------+-----+----+-------------+---+-------+---+----+-----+
|1 |PRIMARY | <derived2> | All | 25 |Using where
+--+-----------+-----+----+-------------+---+-------+---+----+-----+
|2 |DERIVED | <derived3> | All | 25 |Using where
+--+-----------+-----+----+-------------+---+-------+---+----+-----+
|2 |DERIVED | v1 | eq_ref | 1 |using where
+--+-----------+-----+----+-------------+---+-------+---+----+-----+
|3 |DERIVED | tbValue | All | 3072731 |Using temporary, Using filesort
+--+-----------+-----+----+-------------+---+-------+---+----+-----+
rows fetched in 21,0462s
I hope there is someone out there who can help me sort this problem out!
Regards
Martin