I am experiencing very slow deletes when I delete a record from a master table
and have cascading deletes on two detail tables.
I have an application that looks for records in the master table that are older
than "X" days and delete them. The cascasing deletes then handles deleting all
the child records in the other tables. However, this process is very slow.
Depending on how many records are found to delete, this process takes anywhere
from 30-40 minutes to several hours.
Due to the nature of my application, I must loop through the records to delete,
do some stuff for each record, then delete it. I suspect at this point, each
tables' indexes need to be rebuilt. There are several indexes and the ones for
the tables with 4,000,000+ rows probably takes a while.
My question is: What is the best way to handle deleting master/detail records
in this scenario?
I have a brief diagram of my tables and the CREATE TABLE statements follow.
Thanks,
Randall Price
+-------------------+
| tblwsusclientinfo |
+-----------------+ +-------------------+
| tblwsusclients | | ID |
+-----------------+ | UpdateGUID |
| SusClientId |<-----oo| SusClientId |
| ... | | | ... |
+-----------------+ | +-------------------+
( ~ 3,000 rows) | (~ 4,000,000 rows )
|
|
| +-------------------------+
| | tblwsusevents |
| +-------------------------|
| | EventGUID |
| | ... |
+--oo| EventAssociatedComputer |
| ... |
+-------------------------|
(~ 4,300,000 rows )
CREATE TABLE `tblwsusclients` (
`SusClientId` varchar(36) NOT NULL default '',
`DNSName` varchar(256) NOT NULL default '',
`ServerGUID` varchar(36) NOT NULL default '',
`IPAddress` varchar(15) NOT NULL default '',
`LastReportTime` datetime NOT NULL default '0000-00-00 00:00:00',
`LastSyncTime` datetime NOT NULL default '0000-00-00 00:00:00',
`DetectionResult` varchar(256) default NULL,
`ResponsiblePerson` varchar(16) default NULL,
`TargetGroup` varchar(45) default NULL,
`Affiliation` varchar(45) default NULL,
`AddedDate` datetime default NULL,
`IsActive` tinyint(1) default NULL,
`UnRegisteredDate` datetime default NULL,
`SCVersion` double default NULL,
`BiosName` varchar(256) default NULL,
`BiosVersion` varchar(45) default NULL,
`OSVersion` varchar(45) default NULL,
`SPVersion` varchar(45) default NULL,
`Make` varchar(256) default NULL,
`Model` varchar(256) default NULL,
`ProcArchitecture` varchar(45) default NULL,
`OSLongName` varchar(256) default NULL,
`TimedOutDate` datetime default NULL,
PRIMARY KEY (`SusClientId`),
KEY `FK_tblwsusclients_1` (`ServerGUID`),
KEY `IX_DNSName` (`DNSName`),
KEY `IX_IsActive` (`IsActive`),
CONSTRAINT `FK_tblwsusclients_1` FOREIGN KEY (`ServerGUID`) REFERENCES
`tblwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `tblwsusclientinfo` (
`ID` bigint(20) unsigned NOT NULL auto_increment,
`UpdateGUID` varchar(36) NOT NULL default '',
`SusClientId` varchar(36) NOT NULL default '',
`UpdateState` varchar(256) NOT NULL default '',
`LastTimeChanged` datetime default NULL,
PRIMARY KEY (`ID`),
KEY `IX_UpdateState` (`UpdateState`),
KEY `IX_SusClientId_UpdateState` (`SusClientId`,`UpdateState`),
KEY `FK_tblwsusclientinfo_1` (`UpdateGUID`),
KEY `FK_tblwsusclientinfo_2` (`SusClientId`),
CONSTRAINT `FK_tblwsusclientinfo_1` FOREIGN KEY (`UpdateGUID`) REFERENCES
`tblupdateinformation` (`UpdateGUID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_tblwsusclientinfo_2` FOREIGN KEY (`SusClientId`) REFERENCES
`tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `tblwsusevents` (
`EventGUID` varchar(36) NOT NULL default '',
`EventCreationDate` datetime NOT NULL default '0000-00-00 00:00:00',
`EventMessage` text NOT NULL,
`EventId` varchar(256) NOT NULL default '',
`EventSource` varchar(45) NOT NULL default '',
`EventErrorCode` varchar(45) default NULL,
`EventIsError` tinyint(1) NOT NULL default '0',
`EventAssociatedUpdate` varchar(36) default NULL,
`EventAssociatedComputer` varchar(36) default NULL,
`EventAssociatedWSUSServer` varchar(36) NOT NULL default '',
PRIMARY KEY (`EventGUID`),
KEY `IX_EventId` (`EventId`),
KEY `IX_EventCreationDate` (`EventCreationDate`),
KEY `FK_tblwsusevents_1` (`EventAssociatedComputer`),
CONSTRAINT `FK_tblwsusevents_1` FOREIGN KEY (`EventAssociatedComputer`)
REFERENCES `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
