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