220.127.116.11 ANALYZE TABLE Statement
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...
ANALYZE TABLE works with
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
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
ANALYZE TABLE returns a result set with the columns shown in the following table.
||The table name|
||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
STATISTICS table. See Section 18.104.22.168, “SHOW INDEX Statement”, and Section 24.24, “The INFORMATION_SCHEMA STATISTICS Table”.
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 22.214.171.124, “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).
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
For more information about key distribution analysis in
InnoDB, see Section 126.96.36.199, “Configuring Persistent Optimizer Statistics Parameters”, and Section 188.8.131.52, “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.