ORDER BY と組み合わされた DISTINCT では多くの場合に一時テーブルが必要です。

DISTINCT では GROUP BY を使用できるため、MySQL が ORDER BY または HAVING 句内の選択したカラムの部分でないカラムをどのように処理するかを学んでください。セクション12.19.3「MySQL での GROUP BY の処理」を参照してください。

ほとんどの場合、DISTINCT 句は GROUP BY の特殊な例と考えることができます。たとえば、次の 2 つのクエリーは同等です。

WHERE c1 > const;

SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;

この同等性のため、GROUP BY クエリーに適用できる最適化は DISTINCT 句のあるクエリーにも適用できます。そのため、DISTINCT クエリー最適化の可能性の詳細については、セクション8.2.1.16「GROUP BY の最適化」を参照してください。

LIMIT row_countDISTINCT と組み合わせた場合、MySQL は row_count 固有の行が見つかるとただちに停止します。

クエリーに指定されたすべてのテーブルのカラムを使用しない場合、MySQL は最初の一致が見つかるとただちに未使用テーブルのスキャンを停止します。次の例では、t1t2 の前に使用され (これは、EXPLAIN で確認できます)、MySQL は t2 (t1 内の特定の行の) で、最初の行を見つけると、t2 からの読み取りを停止します。

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb
User Comments
  Posted by Mark on August 21, 2006
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.
  Posted by Galt Barber on August 26, 2006
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.)
Sign Up Login You must be logged in to post a comment.