I was working with a mail archive MySQL database today and was twiddling my thumbs waiting for simple queries to complete. The database has about 12 million rows and is on a 2x2GHz 2GB Linux Server x64. I wanted to try to optimize the database and found this little gem from Lee at SoftLayer blog post.
CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA,
CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'MB') FREE
TABLE_SCHEMA NOT IN ('information_schema','mysql')
Data_free > 0
After you run the SQL on your database you can use
optimize table_name to optimize it.
My performance increased enough to be noticeable. =)