13.7.2.1 ANALYZE TABLE Statement
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
ANALYZE TABLE
performs a key distribution analysis and stores the distribution for the named table or tables. For MyISAM
tables, this statement is equivalent to using myisamchk --analyze.
This statement requires SELECT
and INSERT
privileges for the table.
ANALYZE TABLE
works with InnoDB
, NDB
, and MyISAM
tables. It does not work with views.
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 13.1.8, “ALTER TABLE Statement”, and Section 22.3.4, “Maintenance of Partitions”.
During the analysis, the table is locked with a read lock for InnoDB
and MyISAM
.
ANALYZE TABLE
removes the table from the table definition cache, which requires a flush lock. If there are long running statements or transactions still using the table, subsequent statements and transactions must wait for those operations to finish before the flush lock is released. Because ANALYZE TABLE
itself typically finishes quickly, it may not be apparent that delayed transactions or statements involving the same table are due to the remaining flush lock.
By default, the server writes ANALYZE TABLE
statements to the binary log so that they replicate to replicas. To suppress logging, specify the optional NO_WRITE_TO_BINLOG
keyword or its alias LOCAL
.
ANALYZE TABLE
returns a result set with the columns shown in the following table.
Column | Value |
---|---|
Table |
The table name |
Op |
Always analyze |
Msg_type |
status , error , info , note , or warning |
Msg_text |
An informational message |
If the table has not changed since the last key distribution analysis, the table is not analyzed again.
MySQL uses the stored key distribution to decide the table join order for joins 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.
To check the stored key distribution cardinality, use the SHOW INDEX
statement or the INFORMATION_SCHEMA
STATISTICS
table. See Section 13.7.5.22, “SHOW INDEX Statement”, and Section 24.24, “The INFORMATION_SCHEMA STATISTICS Table”.
For InnoDB
tables, ANALYZE TABLE
determines index cardinality by performing random dives on each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE
could produce different numbers. This makes ANALYZE TABLE
fast on InnoDB
tables but not 100% accurate because it does not take all rows into account.
You can make the statistics collected by ANALYZE TABLE
more precise and more stable by enabling innodb_stats_persistent
, as explained in Section 14.8.11.1, “Configuring Persistent Optimizer Statistics Parameters”. When innodb_stats_persistent
is enabled, it is important to run ANALYZE TABLE
after major changes to index column data, as statistics are not recalculated periodically (such as after a server restart).
If innodb_stats_persistent
is enabled, you can change the number of random dives by modifying the innodb_stats_persistent_sample_pages
system variable. If innodb_stats_persistent
is disabled, modify innodb_stats_transient_sample_pages
instead.
For more information about key distribution analysis in InnoDB
, see Section 14.8.11.1, “Configuring Persistent Optimizer Statistics Parameters”, and Section 14.8.11.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”.
MySQL uses index cardinality estimates in join optimization. If a join is not optimized in the right way, try running ANALYZE TABLE
. In the few cases that ANALYZE TABLE
does not produce values good enough for your particular tables, you can use FORCE INDEX
with your queries to force the use of a particular index, or set the max_seeks_for_key
system variable to ensure that MySQL prefers index lookups over table scans. See Section B.4.5, “Optimizer-Related Issues”.
ANALYZE TABLE
clears table statistics from the INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
table and sets the STATS_INITIALIZED
column to Uninitialized
. Statistics are collected again the next time the table is accessed.