4.5.2 ANALYZE TABLE Syntax

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name...]

Analyse and store the key distribution for the table. During the analysis, the table is locked with a read lock. This works on MyISAM and BDB tables.

This is equivalent to running myisamchk -a on the table.

MySQL uses the stored key distribution to decide in which order tables should be joined when one does a join on something else than a constant.

The command returns a table with the following columns:

You can check the stored key distribution with the SHOW INDEX command. See section 4.5.7.1 Retrieving information about Database, Tables, Columns, and Indexes.

If the table hasn't changed since the last ANALYZE TABLE command, the table will not be analysed again.

Strictly before MySQL 4.1.1, ANALYZE commands are not written to the binary log. Since MySQL 4.1.1 they are written to the binary log unless the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) was used.

User Comments

Note that ANALYZE TABLE doesn't seem to be widely
implemented.

I am testing 3.23.49 and 3.23.46 with ISAM tables,
and isamchk -a does the trick, while ANALYZE TABLE
returns an error: "The handler for the table
doesn't support check/repair".

I found this thread
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=8ie85j%2414li%241%40FreeBSD.csie.NCTU.edu.tw&rnum=2&prev=/groups%3Fq%3Dmysql%2Banalyze%2B%2522The%2Bhandler%2Bfor%2Bthe%2Btable%2Bdoesn%27t%2Bsupport%2Bcheck/repair%2522%26hl%3Den%26lr%3D%26ie%3DUTF-8%26sa%3DN%26tab%3Ddg
in mailing.database.mysql enlightening: it might
be a case where documentation talks about a future
feature, instead of an existing one.

For the above comment, please note that analyze is only for MyISAM and BDB tables, not ISAM.

Add your own comment.