From: Randall Price
Date: March 11 2010 3:27pm
Subject: Very slow delete for Master / Child tables with millions of rows
List-Archive: http://lists.mysql.com/mysql/220914
Message-Id: <9269AB049ED62741A28FBF28706E31D32597516D9F@fangorn.cc.w2k.vt.edu>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_000_9269AB049ED62741A28FBF28706E31D32597516D9Ffangornccw2kv_"

--_000_9269AB049ED62741A28FBF28706E31D32597516D9Ffangornccw2kv_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I am experiencing very slow deletes when I delete a record from a master ta=
ble and have cascading deletes on two detail tables.

I have an application that looks for records in the master table that are o=
lder than "X" days and delete them.  The cascasing deletes then handles del=
eting 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 del=
ete, do some stuff for each record, then delete it.  I suspect at this poin=
t, 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 reco=
rds 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 `t=
blwsusservers` (`ServerGUID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1



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`) REFERENCE=
S `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1



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`) R=
EFERENCES `tblwsusclients` (`SusClientId`) ON DELETE CASCADE ON UPDATE CASC=
ADE
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1


--_000_9269AB049ED62741A28FBF28706E31D32597516D9Ffangornccw2kv_--