Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to know what are the benefits [really practical] that can be reaped by running the OPTIMIZE TABLE tbl_name query in MySQL Server.

I checked this once and found that after this is run, the next DB hit takes a long time may be because of the relocation of fragments or so, but subsequent hits show kind of performance, i am not sure whether the query caching does this trick with optimization or optimization alone does this trick.

Can any one guide me with some real performance difference values if possible so that i can take up further as working with MySQL is gaining gravity in our project.

share|improve this question

1 Answer 1

Please keep in mind that OPTIMIZE TABLE does not perform defragmentation. Internally, OPTIMIZE TABLE perform several operations (copying data to a temp file, recreate indexes, recompute index statistics). In fact, the example I have can be performed manually as shown.

Example: If you optimize mydb.mytable, you enter this command:

OPTIMIZE TABLE mydb.mytable;

Note that mysql performs something the following under the hood:

CREATE TABLE mydb.mytable2 LIKE mydb.mytable;
ALTER TABLE mydb.mytable2 DISABLE KEYS;
INSERT INTO mydb.mytable2 SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable2 ENABLE KEYS;
DROP TABLE mydb.mytable;
ALTER TABLE mydb.mytable2 RENAME mydb.mytable;
ANALYZE TABLE mydb.mytable;

This is quite useful for tables that experience a high volume of UPDATEs and DELETEs

Performing this can accomplish two things

  1. Prevent mysql from looking through fragments in a table in an attempt to load data into the right sized fragments. Eliminating these fragments will reduce this operation.

  2. Having the index statistics recomputed helps the MySQL Query Optimizer construct better EXPLAIN plans. Otherwise, queries may deteriorate in execution time because the MySQL Query Optimizer decided to take bad guesses at the EXPLAIN plan. This would be a definite symptom of a table that has had a high volume of UPDATEs and DELETEs.

CAVEAT

With regard to caching, caching takes a dive quickly because of doing a full table scan. For MyISAM index pages flow in and out of the MyISAM Key Cache. For InnoDB, data and index pages flow in and out of the InnoDB Buffer Pool.

share|improve this answer
    
Thanks for your reply. From your point of view, i understand that i better use some service like a cron job to schedule optimize table for one of my frequently updated table so that i can achieve better performance. Further to this i am using InnoDB for this table. Is this a better choice. Also, I find HASH joins in SQL Server which was suggested to kinda improve query performance, can you please explain this for me and how to get a similar one to this in MySQL. Also please give me a SQL Query optimizer for MySQL [Windows7 version]. –  saravanan Jun 30 '11 at 3:36
    
@savaranan : The MySQL Query Optimizer I was referring to was the internal one built into MySQL. BTW Since the table you want to optimize is InnoDB, you can skip the DISABLE KEYS and ENABLE KEYS steps. Also, ANALYZE TABLE can be skipped because it is totally useless on InnoDB tables since InnoDB recomputes its table cardinalities by close approximations using the pages from the BTREE indexes, as known as index dives. –  RolandoMySQLDBA Jun 30 '11 at 15:42
    
@savaranan : As far as HASH indexes go, InnoDB has adapative hash indexes (dev.mysql.com/doc/refman/5.5/en/innodb-adaptive-hash.html). There are also nice suggestions on emulating your own hash indexes and handling collisions based on pages 103-106 of "High Performance MySQL" (amazon.com/dp/0596101716) –  RolandoMySQLDBA Jun 30 '11 at 15:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.