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
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.
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.
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.