Re: Optimizing left outer join query
Posted by:
Toa Sty
Date: September 26, 2006 03:35AM
>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
Subject
Views
Written By
Posted
14297
September 24, 2006 11:40PM
4796
September 25, 2006 03:00AM
4264
September 25, 2006 04:54AM
4513
September 25, 2006 08:09AM
3772
September 25, 2006 09:55PM
Re: Optimizing left outer join query
3608
September 26, 2006 03:35AM
3281
September 26, 2006 03:46AM
5114
September 26, 2006 04:06AM
3163
September 26, 2006 06:31PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.