MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance problem when table gets bigger
2138
July 11, 2005 08:17AM


Sorry, you can't reply to this topic. It has been closed.

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.