On Fri, Apr 11, 2008 at 8:49 AM, Ryan Stille <[EMAIL PROTECTED]> wrote: > I'm trying to delete some orphaned records from a table that has about 150K > records. Here is my delete: > > |CREATE TEMPORARY TABLE deleteids AS ( > SELECT subTable.ID from subTable > LEFT OUTER JOIN parentTable ON subTable.ID = parentTable.ID > WHERE parentTable.ID IS NULL > ); > DELETE FROM subTable WHERE ID IN (SELECT ID FROM deleteids); > DROP TABLE deleteids;| > > > The DELETE statement itself is whats taking so long. About 90 seconds per > 100 records. Should it be so slow? Thats almost 1 second per record! There > are 10K abandoned records I need to delete. Here is the table definition: > > CREATE TABLE `subTable` ( > `ID` int(11) unsigned NOT NULL auto_increment, > `DonorID` int(10) unsigned NOT NULL default '0', > `MedConID` int(11) unsigned NOT NULL default '0', > `MedConSubID` int(11) unsigned NOT NULL default '0', > `FamilyID` int(11) unsigned NOT NULL default '0', > `cbResult` tinyint(1) unsigned NOT NULL default '0', > `deleted` tinyint(1) unsigned NOT NULL default '0', > PRIMARY KEY (`ID`), > KEY `MedConID` (`MedConID`), > KEY `MedConSubID` (`MedConSubID`), > KEY `FamilyID` (`FamilyID`), > KEY `DonorID` (`DonorID`), > KEY `deleted` (`deleted`) > ) ENGINE=MyISAM AUTO_INCREMENT=292088 DEFAULT CHARSET=latin1;
Sub selects suck in mysql. I would rewrite this query to not use the temp table, and probably using the multi table delete delete syntax. I image a query like DELETE subTable FROM subTable LEFT OUTER JOIN parentTable ON subTable.ID = parentTable.ID WHERE parentTable.ID IS NULL should be quite a bit faster. I have had issues with the multie table delete syntax, so YMMV. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
