Bug #40151 EXPLAIN doesn't show DISTINCT+join optimization
Submitted: 19 Oct 2008 18:12 Modified: 2 May 2013 17:27
Reporter: Sergey Petrunya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0/5.6 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[19 Oct 2008 18:12] Sergey Petrunya 
Description:
http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html says:

"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"

The problem is that DISTINCT+join optimization in not shown in EXPLAIN, one can only detect it by checking query speed or doing arithmetics on the values of Handler_XXX counters.  The result was that this optimization was once unintentionally disabled: BUG#32942, and that can happen in the future.

How to repeat:
See testcase for BUG#32942.

Suggested fix:
Make EXPLAIN show that DISTINCT + join optimization are used.
[2 May 2013 17:27] MySQL Verification Team 
Thank you for the bug report.