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
