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.

SELECT
TABLE_SCHEMA,
TABLE_NAME,
CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA,
CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'MB') FREE
from
information_schema.TABLES
where
TABLE_SCHEMA NOT IN ('information_schema','mysql')
and
Data_free > 0

After you run the SQL on your database you can use optimize table table_name to optimize it.

My performance increased enough to be noticeable. =)

Visited 1 times, 1 visit(s) today