Re: Optimizing left outer join query

[Toa Sty]

>That explain the ALL, but I would have expect an eq_ref instead of ref in the 2nd table, since mfg_no is a unique key.

Yeah, but as p.mfg_no can contain NULLs I don't think MySQL can use the eq_ref join type. If it was a NOT NULL column I think it would.


>And, you should have used p.pid is null instead of p.mfg_no is null since mfg_no column allows null.

I wasn't sure on this one, so I did a bit of testing and in MySQL5 at least, if you LEFT JOIN you won't get any rows returned where p.mfg_no was originally NULL, you'll just get the ones that are NULL as a result of the LEFT JOIN. (I think this is because (v.mfg_no = p.mfg_no) never evaluates to true when p.mfg is originally NULL - you'd have to do something like (v.mfg_no = p.mfg_no OR p.mfg_no IS NULL) for NULLs to end up in your result set as a result of both the LEFT JOIN and the original data)

Toasty

-----------------------------------------
email: 'toasty'*3 at gmail