<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizer-statistics">
      </a>
      10.9.6 Optimizer Statistics
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045223227600">
  </a>
  <a class="indexterm" name="idm46045223226112">
  </a>
  <p>
   The
   <code class="literal">
    column_statistics
   </code>
   data dictionary table
        stores histogram statistics about column values, for use by the
        optimizer in constructing query execution plans. To perform
        histogram management, use the
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE
        TABLE
    </code>
   </a>
   statement.
  </p>
  <p>
   The
   <code class="literal">
    column_statistics
   </code>
   table has these
        characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The table contains statistics for columns of all data types
            except geometry types (spatial data) and
      <a class="link" href="json.html" title="13.5 The JSON Data Type">
       <code class="literal">
        JSON
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The table is persistent so that column statistics need not
            be created each time the server starts.
     </p>
    </li>
    <li class="listitem">
     <p>
      The server performs updates to the table; users do not.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The
   <code class="literal">
    column_statistics
   </code>
   table is not directly
        accessible by users because it is part of the data dictionary.
        Histogram information is available using
   <a class="link" href="information-schema-column-statistics-table.html" title="28.3.11 The INFORMATION_SCHEMA COLUMN_STATISTICS Table">
    <code class="literal">
     INFORMATION_SCHEMA.COLUMN_STATISTICS
    </code>
   </a>
   ,
        which is implemented as a view on the data dictionary table.
   <a class="link" href="information-schema-column-statistics-table.html" title="28.3.11 The INFORMATION_SCHEMA COLUMN_STATISTICS Table">
    <code class="literal">
     COLUMN_STATISTICS
    </code>
   </a>
   has these
        columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       SCHEMA_NAME
      </code>
      ,
      <code class="literal">
       TABLE_NAME
      </code>
      ,
      <code class="literal">
       COLUMN_NAME
      </code>
      : The names of the schema,
            table, and column for which the statistics apply.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       HISTOGRAM
      </code>
      : A
      <a class="link" href="json.html" title="13.5 The JSON Data Type">
       <code class="literal">
        JSON
       </code>
      </a>
      value describing the
            column statistics, stored as a histogram.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Column histograms contain buckets for parts of the range of
        values stored in the column. Histograms are
   <a class="link" href="json.html" title="13.5 The JSON Data Type">
    <code class="literal">
     JSON
    </code>
   </a>
   objects to permit
        flexibility in the representation of column statistics. Here is
        a sample histogram object:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-json"><div class="docs-select-all right" id="sa30339173"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-json"><span class="token punctuation">{</span>
  <span class="token property">"buckets"</span><span class="token operator">:</span> <span class="token punctuation">[</span>
    <span class="token punctuation">[</span>
      <span class="token number">1</span><span class="token punctuation">,</span>
      <span class="token number">0.3333333333333333</span>
    <span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">[</span>
      <span class="token number">2</span><span class="token punctuation">,</span>
      <span class="token number">0.6666666666666666</span>
    <span class="token punctuation">]</span><span class="token punctuation">,</span>
    <span class="token punctuation">[</span>
      <span class="token number">3</span><span class="token punctuation">,</span>
      <span class="token number">1</span>
    <span class="token punctuation">]</span>
  <span class="token punctuation">]</span><span class="token punctuation">,</span>
  <span class="token property">"null-values"</span><span class="token operator">:</span> <span class="token number">0</span><span class="token punctuation">,</span>
  <span class="token property">"last-updated"</span><span class="token operator">:</span> <span class="token string">"2017-03-24 13:32:40.000000"</span><span class="token punctuation">,</span>
  <span class="token property">"sampling-rate"</span><span class="token operator">:</span> <span class="token number">1</span><span class="token punctuation">,</span>
  <span class="token property">"histogram-type"</span><span class="token operator">:</span> <span class="token string">"singleton"</span><span class="token punctuation">,</span>
  <span class="token property">"number-of-buckets-specified"</span><span class="token operator">:</span> <span class="token number">128</span><span class="token punctuation">,</span>
  <span class="token property">"data-type"</span><span class="token operator">:</span> <span class="token string">"int"</span><span class="token punctuation">,</span>
  <span class="token property">"collation-id"</span><span class="token operator">:</span> <span class="token number">8</span>
<span class="token punctuation">}</span></code></pre>
  </div>
  <p>
   Histogram objects have these keys:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       buckets
      </code>
      : The histogram buckets. Bucket
            structure depends on the histogram type.
     </p>
     <p>
      For
      <code class="literal">
       singleton
      </code>
      histograms, buckets contain
            two values:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Value 1: The value for the bucket. The type depends on
                the column data type.
        </p>
       </li>
       <li class="listitem">
        <p>
         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.
        </p>
       </li>
      </ul>
     </div>
     <p>
      For
      <code class="literal">
       equi-height
      </code>
      histograms, buckets
            contain four values:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Values 1, 2: The lower and upper inclusive values for
                the bucket. The type depends on the column data type.
        </p>
       </li>
       <li class="listitem">
        <p>
         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.
        </p>
       </li>
       <li class="listitem">
        <p>
         Value 4: The number of distinct values in the range from
                the bucket lower value to its upper value.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       null-values
      </code>
      : A number between 0.0 and 1.0
            indicating the fraction of column values that are SQL
      <code class="literal">
       NULL
      </code>
      values. If 0, the column contains no
      <code class="literal">
       NULL
      </code>
      values.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       last-updated
      </code>
      : When the histogram was
            generated, as a UTC value in
      <em class="replaceable">
       <code>
        YYYY-MM-DD
            hh:mm:ss.uuuuuu
       </code>
      </em>
      format.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       sampling-rate
      </code>
      : 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).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       histogram-type
      </code>
      : The histogram type:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          singleton
         </code>
         : 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
         <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
          <code class="literal">
           ANALYZE TABLE
          </code>
         </a>
         statement that generated the histogram.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          equi-height
         </code>
         : 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
         <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
          <code class="literal">
           ANALYZE TABLE
          </code>
         </a>
         statement
                that generated the histogram.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       number-of-buckets-specified
      </code>
      : The number
            of buckets specified in the
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE
            TABLE
       </code>
      </a>
      statement that generated the histogram.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       data-type
      </code>
      : 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
      <code class="literal">
       int
      </code>
      ,
      <code class="literal">
       uint
      </code>
      (unsigned integer),
      <code class="literal">
       double
      </code>
      ,
      <code class="literal">
       decimal
      </code>
      ,
      <code class="literal">
       datetime
      </code>
      , or
      <code class="literal">
       string
      </code>
      (includes character and binary
            strings).
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       collation-id
      </code>
      : The collation ID for the
            histogram data. It is mostly meaningful when the
      <code class="literal">
       data-type
      </code>
      value is
      <code class="literal">
       string
      </code>
      . Values correspond to
      <code class="literal">
       ID
      </code>
      column values in the Information
            Schema
      <a class="link" href="information-schema-collations-table.html" title="28.3.6 The INFORMATION_SCHEMA COLLATIONS Table">
       <code class="literal">
        COLLATIONS
       </code>
      </a>
      table.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To extract particular values from the histogram objects, you can
        use
   <a class="link" href="json.html" title="13.5 The JSON Data Type">
    <code class="literal">
     JSON
    </code>
   </a>
   operations. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa39935142"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span>
         <span class="token keyword">TABLE_NAME</span><span class="token punctuation">,</span> <span class="token keyword">COLUMN_NAME</span><span class="token punctuation">,</span>
         <span class="token keyword">HISTOGRAM</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$."data-type"'</span> <span class="token keyword">AS</span> <span class="token string">'data-type'</span><span class="token punctuation">,</span>
         <span class="token function">JSON_LENGTH</span><span class="token punctuation">(</span><span class="token keyword">HISTOGRAM</span><span class="token operator">-</span><span class="token operator">&gt;&gt;</span><span class="token string">'$."buckets"'</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'bucket-count'</span>
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>COLUMN_STATISTICS<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> TABLE_NAME      <span class="token punctuation">|</span> COLUMN_NAME <span class="token punctuation">|</span> data<span class="token punctuation">-</span>type <span class="token punctuation">|</span> bucket<span class="token punctuation">-</span>count <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> country         <span class="token punctuation">|</span> Population  <span class="token punctuation">|</span> int       <span class="token punctuation">|</span>          226 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> city            <span class="token punctuation">|</span> Population  <span class="token punctuation">|</span> int       <span class="token punctuation">|</span>         1024 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> countrylanguage <span class="token punctuation">|</span> Language    <span class="token punctuation">|</span> string    <span class="token punctuation">|</span>          457 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The optimizer uses histogram statistics, if applicable, for
        columns of any data type for which statistics are collected. The
        optimizer applies histogram statistics to determine row
        estimates based on the selectivity (filtering effect) of column
        value comparisons against constant values. Predicates of these
        forms qualify for histogram use:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82032681"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><em class="replaceable">col_name</em> <span class="token operator">=</span> <em class="replaceable">constant</em>
<em class="replaceable">col_name</em> <span class="token operator">&lt;&gt;</span> <em class="replaceable">constant</em>
<em class="replaceable">col_name</em> <span class="token operator">!=</span> <em class="replaceable">constant</em>
<em class="replaceable">col_name</em> <span class="token operator">&gt;</span> <em class="replaceable">constant</em>
<em class="replaceable">col_name</em> <span class="token operator">&lt;</span> <em class="replaceable">constant</em>
<em class="replaceable">col_name</em> <span class="token operator">&gt;=</span> <em class="replaceable">constant</em>
<em class="replaceable">col_name</em> <span class="token operator">&lt;=</span> <em class="replaceable">constant</em>
<em class="replaceable">col_name</em> <span class="token operator">IS</span> <span class="token boolean">NULL</span>
<em class="replaceable">col_name</em> <span class="token operator">IS</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<em class="replaceable">col_name</em> <span class="token operator">BETWEEN</span> <em class="replaceable">constant</em> <span class="token operator">AND</span> <em class="replaceable">constant</em>
<em class="replaceable">col_name</em> <span class="token operator">NOT</span> <span class="token operator">BETWEEN</span> <em class="replaceable">constant</em> <span class="token operator">AND</span> <em class="replaceable">constant</em>
<em class="replaceable">col_name</em> <span class="token keyword">IN</span> <span class="token punctuation">(</span><em class="replaceable">constant</em><span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">constant</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span>
<em class="replaceable">col_name</em> <span class="token operator">NOT</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><em class="replaceable">constant</em><span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">constant</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   For example, these statements contain predicates that qualify
        for histogram use:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa19853682"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> orders <span class="token keyword">WHERE</span> amount <span class="token operator">BETWEEN</span> <span class="token number">100.0</span> <span class="token operator">AND</span> <span class="token number">300.0</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tbl <span class="token keyword">WHERE</span> col1 <span class="token operator">=</span> <span class="token number">15</span> <span class="token operator">AND</span> col2 <span class="token operator">&gt;</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The requirement for comparison against a constant value includes
        functions that are constant, such as
   <a class="link" href="mathematical-functions.html#function_abs">
    <code class="literal">
     ABS()
    </code>
   </a>
   and
   <a class="link" href="mathematical-functions.html#function_floor">
    <code class="literal">
     FLOOR()
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa7986903"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> tbl <span class="token keyword">WHERE</span> col1 <span class="token operator">&lt;</span> <span class="token function">ABS</span><span class="token punctuation">(</span><span class="token operator">-</span><span class="token number">34</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Histogram statistics are useful primarily for nonindexed
        columns. Adding an index to a column for which histogram
        statistics are applicable might also help the optimizer make row
        estimates. The tradeoffs are:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      An index must be updated when table data is modified.
     </p>
    </li>
    <li class="listitem">
     <p>
      A histogram is created or updated only on demand, so it adds
            no overhead when table data is modified. On the other hand,
            the statistics become progressively more out of date when
            table modifications occur, until the next time they are
            updated.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The optimizer prefers range optimizer row estimates to those
        obtained from histogram statistics. If the optimizer determines
        that the range optimizer applies, it does not use histogram
        statistics.
  </p>
  <p>
   For columns that are indexed, row estimates can be obtained for
        equality comparisons using index dives (see
   <a class="xref" href="range-optimization.html" title="10.2.1.2 Range Optimization">
    Section 10.2.1.2, “Range Optimization”
   </a>
   ). In this case, histogram
        statistics are not necessarily useful because index dives can
        yield better estimates.
  </p>
  <p>
   In some cases, use of histogram statistics may not improve query
        execution (for example, if the statistics are out of date). To
        check whether this is the case, use
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE
        TABLE
    </code>
   </a>
   to regenerate the histogram statistics, then run
        the query again.
  </p>
  <p>
   Alternatively, to disable histogram statistics, use
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   to drop them. A
        different method of disabling histogram statistics is to turn
        off the
   <a class="link" href="switchable-optimizations.html#optflag_condition-fanout-filter">
    <code class="literal">
     condition_fanout_filter
    </code>
   </a>
   flag
        of the
   <a class="link" href="server-system-variables.html#sysvar_optimizer_switch">
    <code class="literal">
     optimizer_switch
    </code>
   </a>
   system
        variable (although this may disable other optimizations as
        well):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa80297040"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> optimizer_switch<span class="token operator">=</span><span class="token string">'condition_fanout_filter=off'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If histogram statistics are used, the resulting effect is
        visible using
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   . Consider
        the following query, where no index is available for column
   <code class="literal">
    col1
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa5327569"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> col1 <span class="token operator">&lt;</span> <span class="token number">24</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If histogram statistics indicate that 57% of the rows in
   <code class="literal">
    t1
   </code>
   satisfy the
   <code class="literal">
    col1 &lt;
        24
   </code>
   predicate, filtering can occur even in the absence
        of an index, and
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   shows
        57.00 in the
   <code class="literal">
    filtered
   </code>
   column.
  </p>
 </div>
 <br/>
</div>
