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]
