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
