List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:February 5 2008 9:03pm
Subject:RE: Deleting duplicate rows via temporary table either hung or taking way way too long [SOLVED]
View as plain text  
 

> -----Original Message-----
> From: Chris W [mailto:2wsxdr5@stripped] 
> Sent: Monday, February 04, 2008 9:05 PM
> To: Daevid Vincent; MYSQL General List
> Subject: Re: Deleting duplicate rows via temporary table 
> either hung or taking way way too long
> 
> 
> 
> Daevid Vincent wrote:
> > DROP TABLE IF EXISTS `dupes`;
> > CREATE TEMPORARY TABLE dupes 
> > 	SELECT LogID FROM buglog GROUP BY BID, TS HAVING 
> count(*) > 1 ORDER
> > BY BID;
> > LOCK TABLES buglog WRITE;
> > SELECT * FROM buglog WHERE LogID IN (SELECT LogID FROM 
> dupes) LIMIT 10;
> > #DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes);
> > UNLOCK TABLES;
> >
> > The problem is the SELECT (DELETE) is either taking way too 
> long to return
> > or it's hung. I don't sit there long enough to figure it 
> out. It seems like
> > it shouldn't take as long as I wait. If I run the delete 
> version, my buglog
> > table count never decreases in the time I wait.
> >
> >   
> 
> I am pretty sure I have does this in the past and having an 
> index on the 
> temporary table made it amazingly faster.  I assume the LogID 
> field has 
> an index in the other table already,  if not you will want to add an 
> index for that field in that table too.
> 
> The easiest way is to add the index with your create temporary table 
> statement and then do an ...
> INSERT INTO dupes (SELECT .....)

WOW! You are right! That's silly. It's a table with a single column. All
unique.

Anyways, here's the magic incantation that worked for me:

DROP TABLE IF EXISTS `dupes`;
CREATE TEMPORARY TABLE dupes 
	SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) > 1 ORDER
BY BID;
ALTER TABLE `dupes` ADD INDEX `LogID` ( `LogID` ); #//This index is
critical.
DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes);
DROP TABLE IF EXISTS `dupes`;

Trying to use the LOCK TABLES didn't work for me for some reason, but I
didn't care enough to try and debug why. I just wanted the rows gone. :)

Thanks C.W.

Thread
Deleting duplicate rows via temporary table either hung or taking way way too longDaevid Vincent5 Feb
  • Re: Deleting duplicate rows via temporary table either hung or takingway way too longChris W5 Feb
    • RE: Deleting duplicate rows via temporary table either hung or taking way way too long [SOLVED]Daevid Vincent5 Feb
      • Re: Deleting duplicate rows via temporary table either hung or takingway way too long [SOLVED]Chris W5 Feb