<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="group-by-optimization">
      </a>
      10.2.1.17 GROUP BY Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045228793344">
  </a>
  <a class="indexterm" name="idm46045228792272">
  </a>
  <p>
   The most general way to satisfy a
   <code class="literal">
    GROUP BY
   </code>
   clause is to scan the whole table and create a new temporary
          table where all rows from each group are consecutive, and then
          use this temporary table to discover groups and apply
          aggregate functions (if any). In some cases, MySQL is able to
          do much better than that and avoid creation of temporary
          tables by using index access.
  </p>
  <p>
   The most important preconditions for using indexes for
   <code class="literal">
    GROUP BY
   </code>
   are that all
   <code class="literal">
    GROUP
          BY
   </code>
   columns reference attributes from the same index,
          and that the index stores its keys in order (as is true, for
          example, for a
   <code class="literal">
    BTREE
   </code>
   index, but not for a
   <code class="literal">
    HASH
   </code>
   index). Whether use of temporary
          tables can be replaced by index access also depends on which
          parts of an index are used in a query, the conditions
          specified for these parts, and the selected aggregate
          functions.
  </p>
  <p>
   There are two ways to execute a
   <code class="literal">
    GROUP BY
   </code>
   query through index access, as detailed in the following
          sections. The first method applies the grouping operation
          together with all range predicates (if any). The second method
          first performs a range scan, and then groups the resulting
          tuples.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="group-by-optimization.html#loose-index-scan" title="Loose Index Scan">
       Loose Index Scan
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="group-by-optimization.html#tight-index-scan" title="Tight Index Scan">
       Tight Index Scan
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   Loose Index Scan can also be used in the absence of
   <code class="literal">
    GROUP BY
   </code>
   under some conditions. See
   <a class="xref" href="range-optimization.html#range-access-skip-scan" title="Skip Scan Range Access Method">
    Skip Scan Range Access Method
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="loose-index-scan">
       </a>
       Loose Index Scan
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045228778704">
   </a>
   <p>
    The most efficient way to process
    <code class="literal">
     GROUP
            BY
    </code>
    is when an index is used to directly retrieve
            the grouping columns. With this access method, MySQL uses
            the property of some index types that the keys are ordered
            (for example,
    <code class="literal">
     BTREE
    </code>
    ). This property
            enables use of lookup groups in an index without having to
            consider all keys in the index that satisfy all
    <code class="literal">
     WHERE
    </code>
    conditions. This access method
            considers only a fraction of the keys in an index, so it is
            called a
    <span class="firstterm">
     Loose Index
            Scan
    </span>
    . When there is no
    <code class="literal">
     WHERE
    </code>
    clause, a Loose Index Scan reads as many keys as the number
            of groups, which may be a much smaller number than that of
            all keys. If the
    <code class="literal">
     WHERE
    </code>
    clause contains
            range predicates (see the discussion of the
    <a class="link" href="explain-output.html#jointype_range">
     <code class="literal">
      range
     </code>
    </a>
    join type in
    <a class="xref" href="using-explain.html" title="10.8.1 Optimizing Queries with EXPLAIN">
     Section 10.8.1, “Optimizing Queries with EXPLAIN”
    </a>
    ), a Loose Index Scan looks
            up the first key of each group that satisfies the range
            conditions, and again reads the smallest possible number of
            keys. This is possible under the following conditions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The query is over a single table.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        GROUP BY
       </code>
       names only columns that
                form a leftmost prefix of the index and no other
                columns. (If, instead of
       <code class="literal">
        GROUP BY
       </code>
       ,
                the query has a
       <code class="literal">
        DISTINCT
       </code>
       clause, all
                distinct attributes refer to columns that form a
                leftmost prefix of the index.) For example, if a table
       <code class="literal">
        t1
       </code>
       has an index on
       <code class="literal">
        (c1,c2,c3)
       </code>
       , Loose Index Scan is
                applicable if the query has
       <code class="literal">
        GROUP BY c1,
                c2
       </code>
       . It is not applicable if the query has
       <code class="literal">
        GROUP BY c2, c3
       </code>
       (the columns are not
                a leftmost prefix) or
       <code class="literal">
        GROUP BY c1, c2,
                c4
       </code>
       (
       <code class="literal">
        c4
       </code>
       is not in the
                index).
      </p>
     </li>
     <li class="listitem">
      <p>
       The only aggregate functions used in the select list (if
                any) are
       <a class="link" href="aggregate-functions.html#function_min">
        <code class="literal">
         MIN()
        </code>
       </a>
       and
       <a class="link" href="aggregate-functions.html#function_max">
        <code class="literal">
         MAX()
        </code>
       </a>
       , and all of them
                refer to the same column. The column must be in the
                index and must immediately follow the columns in the
       <code class="literal">
        GROUP BY
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Any other parts of the index than those from the
       <code class="literal">
        GROUP BY
       </code>
       referenced in the query must
                be constants (that is, they must be referenced in
                equalities with constants), except for the argument of
       <a class="link" href="aggregate-functions.html#function_min">
        <code class="literal">
         MIN()
        </code>
       </a>
       or
       <a class="link" href="aggregate-functions.html#function_max">
        <code class="literal">
         MAX()
        </code>
       </a>
       functions.
      </p>
     </li>
     <li class="listitem">
      <p>
       For columns in the index, full column values must be
                indexed, not just a prefix. For example, with
       <code class="literal">
        c1 VARCHAR(20), INDEX (c1(10))
       </code>
       , the
                index uses only a prefix of
       <code class="literal">
        c1
       </code>
       values
                and cannot be used for Loose Index Scan.
      </p>
     </li>
    </ul>
   </div>
   <p>
    If Loose Index Scan is applicable to a query, the
    <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
     <code class="literal">
      EXPLAIN
     </code>
    </a>
    output shows
    <code class="literal">
     Using index for group-by
    </code>
    in the
    <code class="literal">
     Extra
    </code>
    column.
   </p>
   <p>
    Assume that there is an index
    <code class="literal">
     idx(c1,c2,c3)
    </code>
    on table
    <code class="literal">
     t1(c1,c2,c3,c4)
    </code>
    . The Loose Index Scan
            access method can be used for the following queries:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78568829"><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> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t1 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c1<span class="token punctuation">,</span> c2<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> c1<span class="token punctuation">,</span> <span class="token function">MIN</span><span class="token punctuation">(</span>c2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c1<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c1 <span class="token operator">&lt;</span> <em class="replaceable">const</em> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c1<span class="token punctuation">,</span> c2<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>c3<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">MIN</span><span class="token punctuation">(</span>c3<span class="token punctuation">)</span><span class="token punctuation">,</span> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c2 <span class="token operator">&gt;</span> <em class="replaceable">const</em> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c1<span class="token punctuation">,</span> c2<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> c2 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c1 <span class="token operator">&lt;</span> <em class="replaceable">const</em> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c1<span class="token punctuation">,</span> c2<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c3 <span class="token operator">=</span> <em class="replaceable">const</em> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c1<span class="token punctuation">,</span> c2<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The following queries cannot be executed with this quick
            select method, for the reasons given:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       There are aggregate functions other than
       <a class="link" href="aggregate-functions.html#function_min">
        <code class="literal">
         MIN()
        </code>
       </a>
       or
       <a class="link" href="aggregate-functions.html#function_max">
        <code class="literal">
         MAX()
        </code>
       </a>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa55249554"><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> c1<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>c2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c1<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The columns in the
       <code class="literal">
        GROUP BY
       </code>
       clause do
                not form a leftmost prefix of the index:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa97897324"><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> c1<span class="token punctuation">,</span> c2 <span class="token keyword">FROM</span> t1 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c2<span class="token punctuation">,</span> c3<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The query refers to a part of a key that comes after the
       <code class="literal">
        GROUP BY
       </code>
       part, and for which there is
                no equality with a constant:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38330306"><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> c1<span class="token punctuation">,</span> c3 <span class="token keyword">FROM</span> t1 <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c1<span class="token punctuation">,</span> c2<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Were the query to include
       <code class="literal">
        WHERE c3 =
        <em class="replaceable">
         <code>
          const
         </code>
        </em>
       </code>
       , Loose Index
                Scan could be used.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The Loose Index Scan access method can be applied to other
            forms of aggregate function references in the select list,
            in addition to the
    <a class="link" href="aggregate-functions.html#function_min">
     <code class="literal">
      MIN()
     </code>
    </a>
    and
    <a class="link" href="aggregate-functions.html#function_max">
     <code class="literal">
      MAX()
     </code>
    </a>
    references already
            supported:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="aggregate-functions.html#function_avg">
        <code class="literal">
         AVG(DISTINCT)
        </code>
       </a>
       ,
       <a class="link" href="aggregate-functions.html#function_sum">
        <code class="literal">
         SUM(DISTINCT)
        </code>
       </a>
       , and
       <a class="link" href="aggregate-functions.html#function_count">
        <code class="literal">
         COUNT(DISTINCT)
        </code>
       </a>
       are
                supported.
       <a class="link" href="aggregate-functions.html#function_avg">
        <code class="literal">
         AVG(DISTINCT)
        </code>
       </a>
       and
       <a class="link" href="aggregate-functions.html#function_sum">
        <code class="literal">
         SUM(DISTINCT)
        </code>
       </a>
       take a
                single argument.
       <a class="link" href="aggregate-functions.html#function_count">
        <code class="literal">
         COUNT(DISTINCT)
        </code>
       </a>
       can have
                more than one column argument.
      </p>
     </li>
     <li class="listitem">
      <p>
       There must be no
       <code class="literal">
        GROUP BY
       </code>
       or
       <code class="literal">
        DISTINCT
       </code>
       clause in the query.
      </p>
     </li>
     <li class="listitem">
      <p>
       The Loose Index Scan limitations described previously
                still apply.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Assume that there is an index
    <code class="literal">
     idx(c1,c2,c3)
    </code>
    on table
    <code class="literal">
     t1(c1,c2,c3,c4)
    </code>
    . The Loose Index Scan
            access method can be used for the following queries:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51629962"><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 function">COUNT</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> c1<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> c1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> c1<span class="token punctuation">,</span> c2<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">COUNT</span><span class="token punctuation">(</span><span class="token keyword">DISTINCT</span> c2<span class="token punctuation">,</span> c1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="tight-index-scan">
       </a>
       Tight Index Scan
      </h5>
     </div>
    </div>
   </div>
   <p>
    A Tight Index Scan may be either a full index scan or a
            range index scan, depending on the query conditions.
   </p>
   <p>
    When the conditions for a Loose Index Scan are not met, it
            still may be possible to avoid creation of temporary tables
            for
    <code class="literal">
     GROUP BY
    </code>
    queries. If there are range
            conditions in the
    <code class="literal">
     WHERE
    </code>
    clause, this
            method reads only the keys that satisfy these conditions.
            Otherwise, it performs an index scan. Because this method
            reads all keys in each range defined by the
    <code class="literal">
     WHERE
    </code>
    clause, or scans the whole index if
            there are no range conditions, it is called a
    <span class="firstterm">
     Tight Index Scan
    </span>
    . With
            a Tight Index Scan, the grouping operation is performed only
            after all keys that satisfy the range conditions have been
            found.
   </p>
   <p>
    For this method to work, it is sufficient that there be a
            constant equality condition for all columns in a query
            referring to parts of the key coming before or in between
            parts of the
    <code class="literal">
     GROUP BY
    </code>
    key. The constants
            from the equality conditions fill in any
    <span class="quote">
     “
     <span class="quote">
      gaps
     </span>
     ”
    </span>
    in the search keys so that it is possible to form complete
            prefixes of the index. These index prefixes then can be used
            for index lookups. If the
    <code class="literal">
     GROUP BY
    </code>
    result
            requires sorting, and it is possible to form search keys
            that are prefixes of the index, MySQL also avoids extra
            sorting operations because searching with prefixes in an
            ordered index already retrieves all the keys in order.
   </p>
   <p>
    Assume that there is an index
    <code class="literal">
     idx(c1,c2,c3)
    </code>
    on table
    <code class="literal">
     t1(c1,c2,c3,c4)
    </code>
    . The following queries do
            not work with the Loose Index Scan access method described
            previously, but still work with the Tight Index Scan access
            method.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       There is a gap in the
       <code class="literal">
        GROUP BY
       </code>
       , but
                it is covered by the condition
       <code class="literal">
        c2 =
                'a'
       </code>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa5784141"><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> c1<span class="token punctuation">,</span> c2<span class="token punctuation">,</span> c3 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c2 <span class="token operator">=</span> <span class="token string">'a'</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c1<span class="token punctuation">,</span> c3<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        GROUP BY
       </code>
       does not begin with the
                first part of the key, but there is a condition that
                provides a constant for that part:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa44899380"><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> c1<span class="token punctuation">,</span> c2<span class="token punctuation">,</span> c3 <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c1 <span class="token operator">=</span> <span class="token string">'a'</span> <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> c2<span class="token punctuation">,</span> c3<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
