Re: I can't say that I've tried this, It works like a champ we do every day in Oracle, DB2 and MySQL. It takes less time because you're bypassing logging thus reduce I/O and locking, etc.
-----Original Message----- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Thursday, February 01, 2007 3:36 PM To: 'Ian Barnes'; mysql@lists.mysql.com Subject: RE: Questions about delete and optimize Another way to do it would be to select the data you want to keep into a table on another file system, truncate the existing table, optimize it, then reload it with the data you saved. I can't say that I've tried this, and have no idea how long it would take or even if it would work. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Ian Barnes [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 01, 2007 4:23 PM > To: mysql@lists.mysql.com > Subject: Questions about delete and optimize > > Hi, > > We are looking at various methods that we can effectively and > efficiently > delete lots of rows from a database and then optimize it. Our > main concern > is disk space - the partition we are working with is only > 12gigs small and > our database vary in size from 1gig (not a problem) to 11gig. > In the example > below I will use one whos .MYD is 6.5 Gig and the .MYI is > 2.7Gig. There are > around 28,900,000 rows in the database. > > Once a month we run an automated program that deletes rows > older than X > months and then we attempt the optimize the table in > question. The delete > query we use is: > DELETE FROM table WHERE date<(current_date - interval 2 month). Now my > questions surrounding this are: > > 1.) Is it quicker to do a query where we say something like: > DELETE FROM > table WHERE date <= '2006-11-01' instead of where date<(current_date)? > 2.) Does the current way we do it use a tmp table that is > written to disk ? > > Then, we run the simple optimize command: OPTIMIZE TABLE > tablename and that > is normally where we come into the problem that mysql tries > to create a tmp > file while optimizing and it runs out of space, and then > corrupts the main > table. We need to run the optimize because after deleting all > those rows, > the space isnt freed up until we run the optimize. So my > other question is > can we do an optimize a different way, or is there some way > that we can > insert and delete rows that would require less optimization? > > Thanks in advance, > Ian > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ******************************************** This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. ******************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
