content_image/797602.jpg
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 Output 

•ANALYZE TABLE Output 

•Key Distribution Analysis 

•Histogram Statistics Analysis 

•Other Considerations 

ANALYZE TABLE  returns a result set with the columns shown in the following table. 

<img src='content_image/797602.jpg'>

## Key Distribution Analysis 

ANALYZE TABLE  without either HISTOGRAM  clause performs a key distribution analysis and stores the distribution for the table or tables. Any existing histogram statistics remain unaffected. 

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 order in which tables should be joined 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.7.22, “SHOW INDEX Statement” , and Section 26.3.34, “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 15.8.10.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. 

