From: Chris W (2wsxdr5cox.net)
Date: Mon Feb 04 2008 - 23:05:10 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]