Pre-General Availability Draft: 2017-05-29
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
ANALYZE TABLE analyzes and stores the key
distribution for a table. During the analysis, the table is
locked with a read lock for
MyISAM. This statement works with
MyISAM tables. For
tables, this statement is equivalent to using myisamchk
--analyze. This statement does not work with views.
system variable is enabled,
TABLE may update the table itself (for example, if
it is a
MyISAM table) but still fail
because it cannot update the dynamic statistics tables in the
data dictionary, which use
obtain the updated statistics, set
For more information on how the analysis works within
Section 220.127.116.11, “Configuring Persistent Optimizer Statistics Parameters” and
Section 18.104.22.168, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”. Also see
Section 22.214.171.124, “Limits on InnoDB Tables”. In particular, when you
you must run
ANALYZE TABLE after loading
substantial data into an
InnoDB table, or
creating a new index for one.
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.
ANALYZE TABLE is supported for partitioned
tables, and you can use
ALTER TABLE ... ANALYZE
PARTITION to analyze one or more partitions; for more
information, see Section 14.1.7, “ALTER TABLE Syntax”, and
Section 21.3.4, “Maintenance of Partitions”.
ANALYZE TABLE returns a result set with the
|The table name|
|An informational message|
If the table has not changed since the last
TABLE statement, the table is not analyzed again.
By default, the server writes
TABLE statements to the binary log so that they
replicate to replication slaves. To suppress logging, specify
NO_WRITE_TO_BINLOG keyword or