<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="distinct-optimization">
      </a>
      10.2.1.18 DISTINCT Optimization
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045228693120">
  </a>
  <a class="indexterm" name="idm46045228692048">
  </a>
  <p>
   <code class="literal">
    DISTINCT
   </code>
   combined with
   <code class="literal">
    ORDER
          BY
   </code>
   needs a temporary table in many cases.
  </p>
  <p>
   Because
   <code class="literal">
    DISTINCT
   </code>
   may use
   <code class="literal">
    GROUP
          BY
   </code>
   , learn how MySQL works with columns in
   <code class="literal">
    ORDER BY
   </code>
   or
   <code class="literal">
    HAVING
   </code>
   clauses that are not part of the selected columns. See
   <a class="xref" href="group-by-handling.html" title="14.19.3 MySQL Handling of GROUP BY">
    Section 14.19.3, “MySQL Handling of GROUP BY”
   </a>
   .
  </p>
  <p>
   In most cases, a
   <code class="literal">
    DISTINCT
   </code>
   clause can be
          considered as a special case of
   <code class="literal">
    GROUP BY
   </code>
   .
          For example, the following two queries are equivalent:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95092955"><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 keyword">DISTINCT</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">&gt;</span> <em class="replaceable">const</em><span class="token punctuation">;</span>

<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">&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> c3<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Due to this equivalence, the optimizations applicable to
   <code class="literal">
    GROUP BY
   </code>
   queries can be also applied to
          queries with a
   <code class="literal">
    DISTINCT
   </code>
   clause. Thus, for
          more details on the optimization possibilities for
   <code class="literal">
    DISTINCT
   </code>
   queries, see
   <a class="xref" href="group-by-optimization.html" title="10.2.1.17 GROUP BY Optimization">
    Section 10.2.1.17, “GROUP BY Optimization”
   </a>
   .
  </p>
  <p>
   When combining
   <code class="literal">
    LIMIT
    <em class="replaceable">
     <code>
      row_count
     </code>
    </em>
   </code>
   with
   <code class="literal">
    DISTINCT
   </code>
   , MySQL stops as soon as it finds
   <em class="replaceable">
    <code>
     row_count
    </code>
   </em>
   unique rows.
  </p>
  <p>
   If you do not use columns from all tables named in a query,
          MySQL stops scanning any unused tables as soon as it finds the
          first match. In the following case, assuming that
   <code class="literal">
    t1
   </code>
   is used before
   <code class="literal">
    t2
   </code>
   (which you can check with
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   ), MySQL stops reading
          from
   <code class="literal">
    t2
   </code>
   (for any particular row in
   <code class="literal">
    t1
   </code>
   ) when it finds the first row in
   <code class="literal">
    t2
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa25567755"><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 keyword">DISTINCT</span> t1<span class="token punctuation">.</span>a <span class="token keyword">FROM</span> t1<span class="token punctuation">,</span> t2 <span class="token keyword">where</span> t1<span class="token punctuation">.</span>a<span class="token operator">=</span>t2<span class="token punctuation">.</span>a<span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
