Why Is This Query A "Select_full_join"?

Bill Karwin <bill@karwin.com> wrote in
news:ekicgm02bbg@enews1.newsguy.com:

> Good Man wrote:
>> 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!
>
> It's kind of grasping at straws in a haystack in the dark, but I'd try
> something like this:
>
> SELECT ue.EmailID, ue.ToAddress, ue.Subject, ue.Message,
> ue.SavedDate, ue.EmailKey, f.FileKey, f.FileName
> FROM UserEmails ue
> LEFT JOIN
> (UserEmailAttachments ua JOIN ProjFiles f ON ua.FileKey =
> f.FileKey)
> ON ue.EmailKey = ua.EmailKey
> WHERE ue.AdminID = 1
> AND ue.SavedDate > 1164344436
> AND ue.SentDate = 0

Hi Bill

I must confess that when I originally posted this, I was hoping that you
would chime in as you've helped quite a few people in this group (have
you considered writing a book?)

I tried the query above, but alas, MySQL just does not want to use the
FileKey index. It's still scanning all the rows in ProjFiles.

I guess I will move on... though this does feel 'unfinished' in a way.
Perhaps when the ProjFiles table grows much larger, MySQL will being to
use an index.

Are any of you running production databases that have the occasional
"Select_full_join" variable increasing every once in a while?

Thanks for everyone's input, much appreciated... glad to know i'm not
insane.

In article <Xns988A6D264207Asonicyouth@216.196.97.131>, heyho@letsgo.com
says...
> Bill Karwin <bill@karwin.com> wrote in
> news:ekicgm02bbg@enews1.newsguy.com:
>
> > Good Man wrote:
> >> 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!
> >
> > It's kind of grasping at straws in a haystack in the dark, but I'd try
> > something like this:
> >
> > SELECT ue.EmailID, ue.ToAddress, ue.Subject, ue.Message,
> > ue.SavedDate, ue.EmailKey, f.FileKey, f.FileName
> > FROM UserEmails ue
> > LEFT JOIN
> > (UserEmailAttachments ua JOIN ProjFiles f ON ua.FileKey =
> > f.FileKey)
> > ON ue.EmailKey = ua.EmailKey
> > WHERE ue.AdminID = 1
> > AND ue.SavedDate > 1164344436
> > AND ue.SentDate = 0
>
> Hi Bill
>
> I must confess that when I originally posted this, I was hoping that you
> would chime in as you've helped quite a few people in this group (have
> you considered writing a book?)
>
> I tried the query above, but alas, MySQL just does not want to use the
> FileKey index. It's still scanning all the rows in ProjFiles.
>
> I guess I will move on... though this does feel 'unfinished' in a way.
> Perhaps when the ProjFiles table grows much larger, MySQL will being to
> use an index.
>
> Are any of you running production databases that have the occasional
> "Select_full_join" variable increasing every once in a while?
>
> Thanks for everyone's input, much appreciated... glad to know i'm not
> insane.
>


Hmm, you all appear to be following my students and overlooking one of
the critical rules-of-thumb for query optimizers:

- place a constant expression (ie ua.uid=1) inside the ON reduces the
table _before_ the join.

- place the same expression in the WHERE and it is checked _after_ the
join (on the combined table).


So... I would expect it to join the entire ua and f tables anyway since
there is no reduction condition, just a link-condition. Possibly
specifying it explicitly as an INNER JOIN would work to check the key,
but I missed the post with your initial query so don't want to jump to
too many conclusions.


For the most optimised possible query, you would in the example above
want to drop the use of WHERE altogether and move its conditions into
the ON. But still, that does not effect the ua/f join in any way.

Ideally your constant expressions should be planned and located to
restrict the largest of the two tables as early as possible. As a
second-best design; _any_ tables reduced before a major join is an
improvement on it.


AJ

TreeNet Admin <admin@treenet.co.nz> wrote in
news:MPG.1ff8a2a727d36ebf989685@news.orcon.net.nz:

I've returned to this problem after shelving it and moving on to other
things... but it still exists and I have to deal with it for real!

> Hmm, you all appear to be following my students and overlooking one of
> the critical rules-of-thumb for query optimizers:
>
> - place a constant expression (ie ua.uid=1) inside the ON reduces
> the table _before_ the join.
>
> - place the same expression in the WHERE and it is checked _after_
> the join (on the combined table).
>
>
> So... I would expect it to join the entire ua and f tables anyway
> since there is no reduction condition, just a link-condition. Possibly
> specifying it explicitly as an INNER JOIN would work to check the key,
> but I missed the post with your initial query so don't want to jump to
> too many conclusions.

Here's the query:

SELECT
ue.EmailID,ue.ToAddress,ue.Subject,ue.Message,ue.S avedDate,ue.EmailKey,f
..FileKey,f.FileName,f.FileSize
FROM UserEmails ue
LEFT JOIN UserEmailAttachments ua ON ue.EmailKey=ua.EmailKey
LEFT JOIN ProjFiles f ON ua.FileKey=f.FileKey
WHERE ue.UserID=870
AND ((ue.SavedDate > 1171876339) AND (ue.SentDate=0))
ORDER BY ue.SavedDate DESC


I don't think I understand your example of placing a 'constant
expression' inside the ON clause. I tried it (I think) but there is
nothing that I can do to avoid the query scanning the entire ProjFiles f
table, even though f.FileKey is indexed. Again, the query is showing
that the index is simply not being used.

> For the most optimised possible query, you would in the example above
> want to drop the use of WHERE altogether and move its conditions into
> the ON. But still, that does not effect the ua/f join in any way.
>
> Ideally your constant expressions should be planned and located to
> restrict the largest of the two tables as early as possible. As a
> second-best design; _any_ tables reduced before a major join is an
> improvement on it.

Can you suggest a query alteration? Or is the increasing
Select_full_join variable now just a part of my life?

Thanks