DISTINCT combined with
BY needs a temporary table in many cases.
DISTINCT may use
BY, you should be aware of how MySQL works with
ORDER BY or
HAVING clauses that are not part of the
selected columns. See Section 12.16.3, “MySQL Handling of GROUP BY”.
In most cases, a
DISTINCT clause can be
considered as a special case of
For example, the following two queries are equivalent:
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 >
const; SELECT c1, c2, c3 FROM t1 WHERE c1 >
constGROUP BY c1, c2, c3;
Due to this equivalence, the optimizations applicable to
GROUP BY queries can be also applied to
queries with a
DISTINCT clause. Thus, for
more details on the optimization possibilities for
DISTINCT queries, see
Section 126.96.36.199, “GROUP BY Optimization”.
DISTINCT, MySQL stops as soon as it finds
row_count unique rows.
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
t1 is used before
(which you can check with
EXPLAIN), MySQL stops reading
t2 (for any particular row in
t1) when it finds the first row in
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;