Hi Ananda,
1. Long term solution: When you upgrade to MySQL 5.1 partition the table
e.g. by time. This approach was used for 10s of years in the other DBs.
http://dev.mysql.com/tech-resources/articles/performance-partitioning.ht
ml
http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning.html
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
2. Short term: There is no point to defragment the table. On the file
system it will be fragmented any way. Just defragment the indexes, which
is faster. May be once a week will be OK.
REPAIR TABLE your_table archive QUICK;
Regards
Theo
-----Original Message-----
From: Ananda Kumar [mailto:anandkl@stripped]
Sent: Monday, 15 September 2008 9:45 PM
To: mysql
Subject: optimize table
I have a table which adds on a daily basis around 3 million records.
We have a automated stored proc, which deletes 1 million reocrds.
Now the optimize table command takes loot of time for me to defragment
the
table.
Is there a any way i can improve the speed of optimize table command.
My mysql version is 5.0.41 and have 16GB RM and 8 CPU's
regards
anandkl
