(mysql.info.gz) DISTINCT optimization

Info Catalog

(mysql.info.gz) IS NULL optimization

(mysql.info.gz) Query Speed

(mysql.info.gz) LEFT JOIN optimization

 
 7.2.8 How MySQL Optimizes `DISTINCT'
 ------------------------------------
 
 `DISTINCT' combined with `ORDER BY' will need a temporary table in many
 cases.
 
 Note that 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.   GROUP-BY-hidden-fields.
 
 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  `GROUP BY' optimization GROUP BY optimization.
 
 When combining `LIMIT ROW_COUNT' with `DISTINCT', MySQL stops as soon
 as it finds ROW_COUNT unique rows.
 
 If you don't use columns from all tables named in a query, MySQL stops
 scanning the not-used 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 the first row in `t2' is found:
 
      SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
 

Info Catalog

(mysql.info.gz) IS NULL optimization

(mysql.info.gz) Query Speed

(mysql.info.gz) LEFT JOIN optimization