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.

What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.

share|improve this question
add comment

2 Answers

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

share|improve this answer
    
I think, optimize table for innodb is not supported. I got a message, index will be recreated. How does it work? –  Boolean Feb 27 '13 at 3:23
    
@RolandoMySQLDBA I'm not sure what mean when you say, "you cannot run OPTIMIZE TABLE." When you OPTIMIZE TABLE for an InnoDB table, MySQL is performing the ALTER TABLE ... ENGINE=InnoDB and ANALYZE TABLE ... operations for you when it says "doing recreate + analyze table instead." –  Michael - sqlbot Feb 27 '13 at 4:14
    
@Michael-sqlbot As shown in my answer, I ran OPTIMIZE TABLE dat; in MySQL 5.5.29 and it complained immediately, Table does not support optimize, doing recreate + analyze instead. That's why I recommend ALTER TABLE dat ENGINE=InnoDB; ANALYZE TABLE dat;. –  RolandoMySQLDBA Feb 27 '13 at 21:27
    
Right, but I would assert that it happened immediately for you because the table you tested against was very small. When you OPTIMIZE TABLE on InnoDB, the server actually does in fact execute ALTER TABLE ... ENGINE=InnoDB and ANALYZE TABLE behind the scenes before returning that response... so you can indeed run OPTIMIZE TABLE on InnoDB, and achieve the intended effect. –  Michael - sqlbot Feb 28 '13 at 3:25
add comment

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.

share|improve this answer
add comment

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.