DISTINCT combined with
BY needs a temporary table in many cases.
DISTINCT may use
BY, learn how MySQL works with columns in
ORDER BY or
clauses that are not part of the selected columns. See
Section 12.17.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 > const GROUP 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 18.104.22.168, “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;