List:General Discussion« Previous MessageNext Message »
From:Chris W Date:February 5 2008 9:57pm
Subject:Re: Deleting duplicate rows via temporary table either hung or taking
way way too long [SOLVED]
View as plain text  
Daevid Vincent wrote:
> WOW! You are right! That's silly. It's a table with a single column. All
> unique.
>   

With out the index MySQL doesn't know they are 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`;
>   

I think what happens if the index isn't there on the dupes table, MySQL 
looks at every row in the buglog table and then does a sequential search 
in the dupes table for that LogID.  So if there there are say 100,000 in 
bug log and say 1000 in dupes that would be 100,000 x 1,000 = 100 
million compares.  If it were to do it the other way around, it would be 
faster.   Take every record in the dupes table and then use the index in 
the buglog table to find the row that matches the LogID.





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