For more information about key distribution analysis in InnoDB , see Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters” , and Section 15.8.10.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.3.5, “Optimizer-Related Issues” . 

## Histogram Statistics Analysis 

ANALYZE TABLE  with the HISTOGRAM  clause enables management of histogram statistics for table column values. For information about histogram statistics, see Section 8.9.6, “Optimizer Statistics” . 

These histogram operations are available: 

• ANALYZE TABLE  with an UPDATE HISTOGRAM  clause generates histogram statistics for the named table columns and stores them in the data dictionary. Only one table name is permitted for this syntax. 

The optional WITH N  BUCKETS  clauses specifies the number of buckets for the histogram. The value of N  must be an integer in the range from 1 to 1024. If this clause is omitted, the number of buckets is 100. 

• ANALYZE TABLE  with a DROP HISTOGRAM  clause removes histogram statistics for the named table columns from the data dictionary. Only one table name is permitted for this syntax. 

Stored histogram management statements affect only the named columns. Consider these statements: 

ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c2, c3 WITH 10 BUCKETS; ANALYZE TABLE t UPDATE HISTOGRAM ON c1, c3 WITH 10 BUCKETS; ANALYZE TABLE t DROP HISTOGRAM ON c2; 

The first statement updates the histograms for columns c1 , c2 , and c3 , replacing any existing histograms for those columns. The second statement updates the histograms for c1  and c3 , leaving the c2  histogram unaffected. The third statement removes the histogram for c2 , leaving those for c1  and c3  unaffected. 

Histogram generation is not supported for encrypted tables (to avoid exposing data in the statistics) or TEMPORARY  tables. 

Histogram generation applies to columns of all data types except geometry types (spatial data) and JSON . 

Histograms can be generated for stored and virtual generated columns. 

Histograms cannot be generated for columns that are covered by single-column unique indexes. 

Histogram management statements attempt to perform as much of the requested operation as possible, and report diagnostic messages for the remainder. For example, if an UPDATE HISTOGRAM  statement names multiple columns, but some of them do not exist or have an unsupported data type, histograms are generated for the other columns, and messages are produced for the invalid columns. 

Histograms are affected by these DDL statements: 

• DROP TABLE  removes histograms for columns in the dropped table. 

• DROP DATABASE  removes histograms for any table in the dropped database because the statement drops all tables in the database. 

