From: Ryan Stille (ryancfwebtools.com)
Date: Fri Apr 11 2008 - 10:49:22 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]