List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:February 5 2008 2:41am
Subject:Deleting duplicate rows via temporary table either hung or taking way way too long
View as plain text  
Having a bit of trouble deleting 8645 duplicate rows...

#//mySQL is broken and you can't reference a table you're deleting from in a
subselect.
#//http://www.thescripts.com/forum/thread490831.html

#// you can't even update said table, so this elegant solution fails too...
#// update buglog set BID = 0 where LogID 
#//	IN (select LogID from buglog group by BID, TS having count(*) > 1
order by BID); 
#// delete from buglog where BID = 0;

So then I tried this hack-method:

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.

mysql> select count(*) from buglog;
+----------+
| count(*) |
+----------+
|    34867 | 
+----------+

mysql> select count(*) from dupes; 
+----------+
| count(*) |
+----------+
|     8645 | 
+----------+

What am I doing wrong?

Is there a better way to delete the duplicate rows?

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