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:[EMAIL PROTECTED] Sent: Monday, June 09, 2003 8:02 AM To: [EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
