List:General Discussion« Previous MessageNext Message »
From:Mike Hillyer Date:June 9 2003 2:49pm
Subject:RE: Batch Deletes...
View as plain text  
You may find that it is the index that is slowing you down. Have you
tried disabling the index(s) on your table before the delete and
re-enabling them after?

Regards,
Mike Hillyer
www.vbmysql.com


-----Original Message-----
From: Todd Gruben [mailto:tgruben@stripped] 
Sent: Monday, June 09, 2003 8:02 AM
To: mysql@stripped
Subject: Batch Deletes...


In an effort to speed up large batch deletes i devised this script.

LOCK TABLES t1 WRITE;
SELECT * INTO OUTFILE '/data/dump/t1' FROM t1 where date>="2003-06-09";
TRUNCATE TABLE t1;
LOAD DATA INFILE '/data/dump/t1' INTO TABLE t1;
UNLOCK TABLES;

this script would work great without the LOCK TABLES clause.  I receive
an 
error saying there active locked tables.  This is a heap table and I am 
running mysql 4.0.12

Any ideas? i need the lock tables because there are some updates that
will be 
happening to the current data, i just need to remove the old expired
data. 
This takes about 30secs and my delete scripts takes about 4 hours. It is

deleting about 500,000 rows out of a 4 million row table with several 
indexes.
-Todd


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Batch Deletes...Todd Gruben9 Jun
  • Re: Batch Deletes...Paul DuBois9 Jun
RE: Batch Deletes...Mike Hillyer9 Jun
Re: Batch Deletes...Paul DuBois9 Jun