(mysql.info) distinct-optimization

Info Catalog

(mysql.info) is-null-optimization

(mysql.info) query-speed

(mysql.info) left-join-optimization

 
 7.2.8 `DISTINCT' Optimization
 -----------------------------
 
 `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  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.
 
 When combining `LIMIT ROW_COUNT' with `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;
 

Info Catalog

(mysql.info) is-null-optimization

(mysql.info) query-speed

(mysql.info) left-join-optimization