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
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
