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.
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;
From: Ananda Kumar [mailto:anandkl@stripped]
Sent: Monday, 15 September 2008 9:45 PM
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
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