(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
automatically generated byinfo2html