More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
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.
(Continued on next question...)
