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:
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.
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 : http://www.scribd.com/doc/31337494/XtraDB-InnoDB-internals-in-drawing
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. You would have to run the equivalent:
ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;
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.
I actually wrote posts about the futility of ANALYZE TABLE on InnoDB:
