DISTINCT
combined with ORDER
BY
needs a temporary table in many cases.
Because DISTINCT
may use GROUP
BY
, you should be aware of how MySQL works with
columns in ORDER BY
or
HAVING
clauses that are not part of the
selected columns. See Section 11.12.3, “GROUP BY
and HAVING
with Hidden
Columns”.
In most cases, a DISTINCT
clause can be
considered as a special case of GROUP BY
. 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 7.2.15, “GROUP BY
Optimization”.
When combining LIMIT
with
row_count
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 t2
(which you can check with
EXPLAIN
), MySQL stops reading
from t2
(for any particular row in
t1
) when it finds the first row in
t2
:
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
User Comments
I have had some trouble with DISTINCT and ORDER BY, for example I could not get:
SELECT DISTINCT id,city FROM clients WHERE city like "k%" ORDER BY id DESC;
to work, as in it would not be correctly ordered.
However, doing this seems to work:
SELECT DISTINCT id,city FROM (clients inner join clients AS c on clients.id=c.id) WHERE city like "k%" ORDER BY id DESC;
I think it has to do with the way distinct is optimized, and if a a field marked in the distinct clause is a primary key, it will ignore it because it is distinct already and then it will ignore the order by. Sure, it doesn't make sense to use DISTINCT if you know you have a primary key. But if you have code where only sometimes this happens, and you don't want to completely recode everything this may be of help to you.
-Mark
Select count(distinct somefield) from sometable is very slow in all versions of mysql.
The distinct part is equivalent to:
select somefield from sometable group by somefield
although the latter sometimes runs even faster than distinct. To get reasonable speed under mysql5, please use this equivalent which uses a subquery:
select count(*) from (select distinct somefield from sometable group by somefield) as somelabel;
This is quite speedy.
(I don't know why count distinct is so slow when distinct itself isn't that slow.)
Add your own comment.