GNU Info

(mysql.info)DISTINCT optimization

---

Next: LEFT JOIN optimization Prev: Where optimizations Up: Query Speed

---

Enter node , (file) or (file)node

How MySQL Optimizes `DISTINCT'
------------------------------

`DISTINCT' is converted to a `GROUP BY' on all columns, `DISTINCT'
combined with `ORDER BY' will in many cases also need a temporary table.

When combining `LIMIT #' with `DISTINCT', MySQL will stop as soon as it
finds `#' unique rows.

If you don't use columns from all used tables, MySQL will stop the
scanning of the not used tables as soon as it has found the first match.

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

In the case, assuming t1 is used before t2 (check with `EXPLAIN'), then
MySQL will stop reading from t2 (for that particular row in t1) when
the first row in t2 is found.