We do confuse between these two terms related to mysql. Here is some light on these two words.
Two major admin tasks related to a table are
1. Shrink: Shrinking a table by recreating it
2. Update cardinality: Updating index statistics or cardinality of indexes in a table.
Now here is the difference in simple words.
ANALYZE = Update cardinality
OPTIMIZE = Shrink + Update cardinality
= Shrink + ANALYZE table.
NOTE:
1. OPTIMIZE is nothing but
ALTER TABLE tbl1 ENGINE=INNODB/MYISAM followed by ANALYZE TABLE tbl1
2. In case of innodb file per table disabled, OPTIMIZE might increase the ibdata file size on disc.
3. In case of myisam or innodb file per table enabled, OPTIMIZE will release some space to OS as srhink
will be performed.
4. After OPTIMIZE, SELECT operation can perform a bit quicker than before as the data and indexes be
written on contiguous memory locations discarding fragmented space.
Two major admin tasks related to a table are
1. Shrink: Shrinking a table by recreating it
2. Update cardinality: Updating index statistics or cardinality of indexes in a table.
Now here is the difference in simple words.
ANALYZE = Update cardinality
OPTIMIZE = Shrink + Update cardinality
= Shrink + ANALYZE table.
NOTE:
1. OPTIMIZE is nothing but
ALTER TABLE tbl1 ENGINE=INNODB/MYISAM followed by ANALYZE TABLE tbl1
2. In case of innodb file per table disabled, OPTIMIZE might increase the ibdata file size on disc.
3. In case of myisam or innodb file per table enabled, OPTIMIZE will release some space to OS as srhink
will be performed.
4. After OPTIMIZE, SELECT operation can perform a bit quicker than before as the data and indexes be
written on contiguous memory locations discarding fragmented space.