"Paul Lautman" <paul.lautman@btinternet.com> wrote in
news:4srceaF10pcc8U1@mid.individual.net:

> Michael Austin wrote:
>> and doesn't he really want f.FileKey = ua.FileKey (as you stated),
>> so, isn't this backwards?
>>
>> "LEFT JOIN ProjFiles f ON ua.FileKey=f.FileKey"
>>
>> this, to me, says look at all records in f first, then match with ua.
>> and since there is no where-clause for f, it does a FTS. Order really
>> does matter.
>
> Have you tried an experiment to prove this statement?
> The order of operands around the equals statement should not matter.
> The JOIN condition is what is stating that one should take all the
> records in ua and lookup the equivalent row in f.

Hello, I appreciate all the comments here.

I tried swapping the columns on either side of the '=', but alas there
was no difference.

I have read about the optimizer sometimes choosing to ignore indexes (or
using an index you wouldn't expect it to use), so I'm *almost* ready to
accept that as an explanation, but seeing anything other than a '0' in
the "Select_full_join" MySQL status variable makes me feel soiled in
some way - I want to take a 14-hour shower!

The thing is, the ProjFiles table (f) is one of the largest in my
database; the 7000 entries I have in there now will surely be much
larger with real life usage.

Would using subqueries make a difference? I've never really explored
them; JOINS have been sufficient for me so far, so I'm still a little
baffled why MySQL isn't using the f.FileKey index....

Can my query be written in any other way? A comment above about
f.FileKey not being in the WHERE clause is the way it has to be!



Thanks