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
Also the table seems to be locked while running this delete... thats not
going to be very good when I need to run it on production. Is there a
way to have it not be locked during this delete?
I'm thinking of creating a script to delete in 10 row increments until
they are all gone.
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
-Original Message-
From: Ryan Stille [mailto:[EMAIL PROTECTED]
Subject: Re: Why is this delete so slow? ( 90 seconds per 100 records)
Also the table seems to be locked while running this delete... thats not
going to be very good when I need to run it on production. Is there a
way
Thanks for the help, I rewrote it as a subselect and it deleted all 10K
records in two seconds.
DELETE subTable FROM subTable LEFT OUTER JOIN parentTable ON
subTable.parentID = parentTable.ID WHERE parentTable.ID IS NULL
-Ryan
--
MySQL General Mailing List
For list archives:
Ryan Stille wrote:
Thanks for the help, I rewrote it as a subselect and it deleted all
10K records in two seconds.
DELETE subTable FROM subTable LEFT OUTER JOIN parentTable ON
subTable.parentID = parentTable.ID WHERE parentTable.ID IS NULL
-Ryan
Whoops, I meant that I rewrote it as a
