Movatterモバイル変換

DISTINCT combined withORDER BY needs a temporary table in many cases.

BecauseDISTINCT may useGROUP BY, learn how MySQL works with columns inORDER BY orHAVING clauses that are not part of the selected columns. SeeSection 14.19.3, “MySQL Handling of GROUP BY”.

In most cases, aDISTINCT clause can be considered as a special case ofGROUP BY. For example, the following two queries are equivalent:

SELECT DISTINCT c1, c2, c3 FROM t1WHERE c1 >const;SELECT c1, c2, c3 FROM t1WHERE c1 >const GROUP BY c1, c2, c3;

Due to this equivalence, the optimizations applicable toGROUP BY queries can be also applied to queries with aDISTINCT clause. Thus, for more details on the optimization possibilities forDISTINCT queries, seeSection 10.2.1.17, “GROUP BY Optimization”.

When combiningLIMITrow_count withDISTINCT, MySQL stops as soon as it findsrow_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 thatt1 is used beforet2 (which you can check withEXPLAIN), MySQL stops reading fromt2 (for any particular row int1) when it finds the first row int2:

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