2) Your OPTIMIZE statement does cause mysql to create a temporary table,
which eventually replaces your current one.
I suggest you try something along the lines of this and compare speed:
- LOCK TABLES ..
- CREATE TABLE `<table_tmp>` (..) # identical table
- INSERT INTO `<table_tmp>` SELECT * FROM `<table>` WHERE date < (NOW())
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
- DROP TABLE `<table>`
- RENAME TABLE `<table_tmp>` TO `<table>`
- UNLOCK TABLES
The above may be faster if you have an index on 'date'.
Atle
On Thu, 1 Feb 2007, Ian Barnes wrote:
> 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]
