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
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
