Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.0Mb
PDF (A4) - 32.9Mb
PDF (RPM) - 30.9Mb
HTML Download (TGZ) - 7.9Mb
HTML Download (Zip) - 7.9Mb
HTML Download (RPM) - 6.7Mb
Man Pages (TGZ) - 143.7Kb
Man Pages (Zip) - 203.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


MySQL 8.0 Reference Manual  /  ...  /  Optimizer Statistics

Pre-General Availability Draft: 2017-08-19

8.9.6 Optimizer Statistics

The column_statistics data dictionary table stores histogram statistics about column values. To perform histogram management, use the ANALYZE TABLE statement; see Section 13.7.2.1, “ANALYZE TABLE Syntax”.

Note

Currently, the optimizer does not yet consult the column_statistics table in the course of query execution plan construction.

The column_statistics table has these characteristics:

  • The table contains statistics for columns of all data types except geometry types (spatial data) and JSON.

  • The table makes column statistics persistent so that they need not be created each time the server starts.

  • The server performs updates to the table; users do not.

The column_statistics table is not directly accessible by users because it is part of the data dictionary. Histogram information is available using INFORMATION_SCHEMA.COLUMN_STATISTICS, which is implemented as a view on the data dictionary table. COLUMN_STATISTICS has these columns:

  • SCHEMA_NAME, TABLE_NAME, COLUMN_NAME: The names of the schema, table, and column for which the statistics apply.

  • HISTOGRAM: A JSON value describing the column statistics, stored as a histogram.

Column histograms contain buckets for parts of the range of values stored in the column. Histograms are JSON objects to permit flexibility in the way column statistics are represented. Here is a sample histogram object:

{
  "buckets": [
    [
      1,
      0.3333333333333333
    ],
    [
      2,
      0.6666666666666666
    ],
    [
      3,
      1
    ]
  ],
  "null-values": 0,
  "last-updated": "2017-03-24 13:32:40.000000",
  "sampling-rate": 1,
  "histogram-type": "singleton",
  "number-of-buckets-specified": 128,
  "data-type": "int",
  "charset-id": 8
}

Histogram objects have these keys:

  • buckets: The histogram buckets. Bucket structure depends on the histogram type.

    For singleton histograms, buckets contain two values:

    • Value 1: The value for the bucket. The type depends on the column data type.

    • Value 2: A double representing the cumulative frequency for the value. For example, .25 and .75 indicate that 25% and 75% of the values in the column are less than or equal to the bucket value.

    For equi-height histograms, buckets contain four values:

    • Values 1, 2: The lower and upper inclusive values for the bucket. The type depends on the column data type.

    • Value 3: A double representing the cumulative frequency for the value. For example, .25 and .75 indicate that 25% and 75% of the values in the column are less than or equal to the bucket upper value.

    • Value 4: The number of distinct values in the range from the bucket lower value to its upper value.

  • null-values: A number between 0.0 and 1.0 indicating the fraction of column values that are SQL NULL values. If 0, the column contains no NULL values.

  • last-updated: When the histogram was generated, as a UTC value in YYYY-MM-DD HH:MM:SS.000000 format (the fractional seconds part is always zero).

  • sampling-rate: A number between 0.0 and 1.0 indicating the fraction of data that was sampled to create the histogram. A value of 1 means that all of the data was read (no sampling).

  • histogram-type: The histogram type:

    • singleton: One bucket represents one single value in the column. This histogram type is created when the number of distinct values in the column is less than or equal to the number of buckets specified in the ANALYZE TABLE statement that generated the histogram.

    • equi-height: One bucket represents a range of values. This histogram type is created when the number of distinct values in the column is greater than the number of buckets specified in the ANALYZE TABLE statement that generated the histogram.

  • number-of-buckets-specified: The number of buckets specified in the ANALYZE TABLE statement that generated the histogram.

  • data-type: The type of data this histogram contains. This is needed when reading and parsing histograms from persistent storage into memory. The value is one of int, uint (unsigned integer), double, decimal, datetime, or string (includes character and binary strings).

  • charset-id: The collation ID for the histogram data. It is mostly meaningful when the data-type value is string. Values correspond to ID column values in the INFORMATION_SCHEMA.COLLATIONS table.


User Comments
Sign Up Login You must be logged in to post a comment.