List:General Discussion« Previous MessageNext Message »
From:Chris W Date:February 5 2008 5:05am
Subject:Re: Deleting duplicate rows via temporary table either hung or taking
way way too long
View as plain text  

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 .....)

-- 
Chris W
KE5GIX

"Protect your digital freedom and privacy, eliminate DRM, 
learn more at http://www.defectivebydesign.org/what_is_drm"

Ham Radio Repeater Database.
http://hrrdb.com

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