> -----Original Message-----
> From: Chris W [mailto:[EMAIL PROTECTED]
> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
