What is the difference between OPTIMIZE TABLE and ANALYZE TABLE table in MySQL

Question

What is the difference between OPTIMIZE TABLE and ANALYZE TABLE table in MySQL? I have read the online documentation but not sure what the difference is.

Answer 1

To expand on @MitchWheat's answer (+1 for directly answering first):

ANALYZE TABLE examines key distribution and stores them in INFORMATION_SCHEMA.STATISTICS.

OPTIMIZE TABLE performs ANALYZE TABLE after doing some table compression. The equivalent of OPTIMIZE TABLE mydb.mytable; if the table was MyISAM is this:

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

MyISAM

For the MyISAM table mydb.mytable in datadir /var/lib/mysql, you have the following files:

• /var/lib/mysql/mydb/mytable.frm
• /var/lib/mysql/mydb/mytable.MYD (data)
• /var/lib/mysql/mydb/mytable.MYI (indexes)

OPTIMIZE TABLE mydb.mytable would shrink the .MYD and .MYI files for the table.

This is not the same for InnoDB. Here is how it is different:

InnoDB (innodb_file_per_table enabled)

Each table's data and indexes are stored in an external tablespace file. For datadir is /var/lib/mysql and the table mydb.mytable, it would be stored as follows:

• /var/lib/mysql/mydb/mytable.frm
• /var/lib/mysql/mydb/mytable.ibd

When OPTIMIZE TABLE mydb.mytable is executed, mytable.ibd gets shrunk.

InnoDB (innodb_file_per_table disabled)

Only /var/lib/mysql/mydb/mytable.frm would exist. All the data and index pages for the table mydb.mytable are stored in the system tablespace file /var/lib/mysql/ibdata1.

When OPTIMIZE TABLE mydb.mytable is executed, the data and index pages are written contiguously in ibdata1. Unfortunately, this make ibdata1 grow in leaps and bounds.

See the Pictorial Representation from Percona CTO Vadim Tkachenko

UPDATE 2013-02-26 22:33 EST

You comment was

I think, optimize table for innodb is not supported. I got a message, index will be recreated. How does it work?

I tried this out

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table dat (a int, primary key (a));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into dat values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.04 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> analyze table dat;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| test.dat | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.06 sec)

mysql> optimize table dat;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.dat | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.dat | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.14 sec)

mysql>

You are correct. You cannot run OPTIMIZE TABLE as a single operation. What InnoDB does instead is the following:

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;

You could also just run these steps yourself.

However, in all honesty, you should not have to run ANALYZE TABLE against an InnoDB table because each time a query is executed, the InnoDB Storage Engine performs an estimation on table cardinality based on passing through pages in the indexes. If there are a high number of INSERTs, UPDATEs, and DELETEs, then you will need to ANALYZE TABLE. When there is a high number of DELETEs, then ALTER TABLE mydb.mytable ENGINE=InnoDB; is needed to shrink the table.

I actually wrote posts about the futility of ANALYZE TABLE on InnoDB in certain instances:

• Oct 16, 2011 : Suddenly have to rebuild indexes to prevent site from going down
• Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

Answer 2

Depends on your version of MySQL and storage engine but in general:

OPTIMIZE TABLE Analyzes table, stores the key distribution for a table, reclaims the unused space and defragments the data file.

ANALYZE TABLE Only analyzes table and stores the key distribution.