List:General Discussion« Previous MessageNext Message »
From:Ryan Stille Date:April 11 2008 3:49pm
Subject:Why is this delete so slow? ( 90 seconds per 100 records)
View as plain text  
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;


Thread
Why is this delete so slow? ( 90 seconds per 100 records)Ryan Stille11 Apr
  • Re: Why is this delete so slow? ( 90 seconds per 100 records)Ryan Stille11 Apr
    • RE: Why is this delete so slow? ( 90 seconds per 100 records)Jerry Schwartz11 Apr
      • Re: Why is this delete so slow? ( 90 seconds per 100 records)Ryan Stille11 Apr
        • Re: Why is this delete so slow? ( 90 seconds per 100 records)Ryan Stille11 Apr
  • Re: Why is this delete so slow? ( 90 seconds per 100 records)Rob Wultsch11 Apr