ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLEtbl_name
[,tbl_name
] ...
ANALYZE TABLE
analyzes and stores
the key distribution for a table. During the analysis, the table
is locked with a read lock for MyISAM
. For
InnoDB
the table is locked with a write lock.
This statement works with MyISAM
and
InnoDB
tables. For MyISAM
tables, this statement is equivalent to using myisamchk
--analyze.
For more information on how the analysis works within
InnoDB
, see
Section 13.6.14, “Restrictions on InnoDB
Tables”.
MySQL Enterprise. For expert advice on optimizing tables, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
MySQL uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.
This statement requires SELECT
and INSERT
privileges for the
table.
Beginning with MySQL 5.1.27, ANALYZE
TABLE
is also supported for partitioned tables. Also
beginning with MySQL 5.1.27, you can use ALTER TABLE
... ANALYZE PARTITION
to analyze one or more
partitions; for more information, see
Section 12.1.7, “ALTER TABLE
Syntax”, and
Section 18.3.3, “Maintenance of Partitions”.
ANALYZE TABLE
returns a result
set with the following columns.
Column | Value |
Table |
The table name |
Op |
Always analyze
|
Msg_type |
status , error ,
info , or warning
|
Msg_text |
An informational message |
You can check the stored key distribution with the
SHOW INDEX
statement. See
Section 12.4.5.23, “SHOW INDEX
Syntax”.
If the table has not changed since the last
ANALYZE TABLE
statement, the
table is not analyzed again.
By default, ANALYZE TABLE
statements are written to the binary log so that they will be
replicated to replication slaves. Logging can be suppressed with
the optional NO_WRITE_TO_BINLOG
keyword or
its alias LOCAL
.
User Comments
Use mysqlcheck -Aa -uroot -p to run analyze table for all databases and tables (including InnoDB) on a running server. Available in MySQL 3.23.38 and later.
Note that ANALYZE TABLE requires SELECT and INSERT privileges, only (LOCK TABLES is not needed).
Add your own comment.