Professional Web Applications Themes

Why Is This Query A "Select_full_join"? - MySQL

Hi there While getting some undesired results from a query, I looked a little closer at it and was surprised when my EXPLAIN statement told me that my query was looking through all the rows in a particular table, and no keys were being used on this table - "possible_keys" and "key" are both NULL for this table in the EXPLAIN statement. Sure enough, the "Select_full_join" variable in MySQL Administrator increments by one every time I use the query. I can't figure out WHY this is happening, as I have an index on the table that should be used.... here ...

  1. #1

    Default Why Is This Query A "Select_full_join"?

    Hi there

    While getting some undesired results from a query, I looked a little
    closer at it and was surprised when my EXPLAIN statement told me that my
    query was looking through all the rows in a particular table, and no
    keys were being used on this table - "possible_keys" and "key" are both
    NULL for this table in the EXPLAIN statement. Sure enough, the
    "Select_full_join" variable in MySQL Administrator increments by one
    every time I use the query.

    I can't figure out WHY this is happening, as I have an index on the
    table that should be used.... here is my query:

    SELECT
    ue.EmailID,ue.ToAddress,ue.Subject,ue.Message,ue.S avedDate,ue.EmailKey,
    f.FileKey,f.FileName
    FROM UserEmails ue
    LEFT JOIN UserEmailAttachments ua ON ue.EmailKey=ua.EmailKey
    LEFT JOIN ProjFiles f ON ua.FileKey=f.FileKey
    WHERE ue.AdminID=1 AND ((ue.SavedDate > 1164344436) AND (ue.SentDate=0))

    Basically, what I want to be doing here is grabbing a users 'email
    message' in the database. LEFT JOINS are used on the
    UserEmailAttachments and ProjFiles tables because an 'email' may or may
    not have any files attached to it.

    The problem table in the EXPLAIN statement is ProjFiles. It has 7803
    rows so far, and they're all being looked at, even though the FileKey
    column (which is the one being checked in the query) is indexed:

    CREATE TABLE `ProjFiles` (
    `FileID` int(10) unsigned NOT NULL auto_increment,
    `FolderID` int(10) unsigned NOT NULL default '0',
    `ClientID` int(10) unsigned NOT NULL default '0',
    `FileSetID` mediumint(8) unsigned NOT NULL default '0',
    `FileType` varchar(255) NOT NULL default '',
    `FileSize` varchar(20) NOT NULL default '0',
    `VersionNum` varchar(10) default NULL,
    `Note` text,
    `AddedBy` int(10) unsigned default NULL,
    `AddedByUser` int(10) unsigned default NULL,
    `DateAdded` varchar(100) default 'May 15th, 2005',
    `DateStamp` bigint(20) unsigned NOT NULL default '0',
    `FileName` varchar(250) NOT NULL default '',
    `FilePath` text NOT NULL,
    `FileKey` varchar(30) NOT NULL default '',
    PRIMARY KEY (`FileID`),
    KEY `CliDex` (`ClientID`),
    KEY `FoldDex` (`FolderID`),
    KEY `SetDex` (`FileSetID`),
    KEY `KeyDex` (`FileKey`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    Any tips? Thanks.


    Good Guest

  2. #2

    Default Re: Why Is This Query A "Select_full_join"?


    Good Man wrote: 


    because you do not have an index on AdminID, SaveDate and SentDate.

    create index ue_idx on `UserEmails` (`AdminID`, `SaveDate`,
    `SentDate`).....

    onedbguru@yahoo.com Guest

  3. #3

    Default Re: Why Is This Query A "Select_full_join"?

    com wrote in news:1164392562.354785.112340
    f16g2000cwb.googlegroups.com:
     


    That doesn't help at all, and I can't see why it would considering that the
    table causing problems isn't UserEmails but ProjFiles.... the ProjFiles
    table is the one being scanned.


    Good Guest

  4. #4

    Default Re: Why Is This Query A "Select_full_join"?

    Good Man wrote:
     
    >
    >
    >
    > That doesn't help at all, and I can't see why it would considering that the
    > table causing problems isn't UserEmails but ProjFiles.... the ProjFiles
    > table is the one being scanned.
    >
    >[/ref]

    sorry, overlooked that in your description...

    What in your query makes some rows in ProjFiles more selectable? Since you did
    not exclude or include any ProjFiles values in your where clause, of course it
    needs to scan the whole table.


    --
    Michael Austin.
    Database Consultant
    Michael Guest

  5. #5

    Default Re: Why Is This Query A "Select_full_join"?

    Michael Austin wrote: 
    >>
    >>
    >>
    >> That doesn't help at all, and I can't see why it would considering
    >> that the table causing problems isn't UserEmails but ProjFiles....
    >> the ProjFiles table is the one being scanned.
    >>
    >>[/ref]
    >
    > sorry, overlooked that in your description...
    >
    > What in your query makes some rows in ProjFiles more selectable?
    > Since you did not exclude or include any ProjFiles values in your
    > where clause, of course it needs to scan the whole table.[/ref]

    I think you are mising his point Michael. He is doing a JOIN to that table.
    So the only records that need to be fetched from ProjFiles are those that
    have f.FileKey = ua.FileKey. So he is expecting the index to be used to
    locate the indovidual f.FileKey values, rather than having to scan the table
    for them.


    Paul Guest

  6. #6

    Default Re: Why Is This Query A "Select_full_join"?

    Paul Lautman wrote: 
    >>
    >>sorry, overlooked that in your description...
    >>
    >>What in your query makes some rows in ProjFiles more selectable?
    >>Since you did not exclude or include any ProjFiles values in your
    >>where clause, of course it needs to scan the whole table.[/ref]
    >
    >
    > I think you are mising his point Michael. He is doing a JOIN to that table.
    > So the only records that need to be fetched from ProjFiles are those that
    > have f.FileKey = ua.FileKey. So he is expecting the index to be used to
    > locate the indovidual f.FileKey values, rather than having to scan the table
    > for them.
    >
    >[/ref]


    Optimizers are a funny thing. They don't always do what we think they should do.
    Maybe the cardinality of the selectivity is such that the optimizer determines
    it can get the data faster by doing a FTS.

    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.

    --
    Michael Austin.
    Database Consultant
    Michael Guest

  7. #7

    Default Re: Why Is This Query A "Select_full_join"?

    Michael Austin wrote: 

    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.
    if the optimiser is sensitive to a=5 vs 5=a then it needs serious looking
    at.


    Paul Guest

  8. #8

    Default Re: Why Is This Query A "Select_full_join"?

    "Paul Lautman" <com> wrote in
    news:individual.net:
     
    >
    > 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.[/ref]

    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
    Good Guest

  9. #9

    Default Re: Why Is This Query A "Select_full_join"?

    Good Man wrote: 
    I would try deconstructing the query and building it up bit by bit. First
    try with a basic where and see if the index gets used.



    Paul Guest

  10. #10

    Default Re: Why Is This Query A "Select_full_join"?

    Good Man wrote: 

    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

    I'm assuming that if a record exists in ua, then there must be a
    matching record in f. Making the join between ua and f could therefore
    be improved by making it an inner join.

    I can't be precise about my reasoning, I just think this may give the
    optimizer an "easier" task to yze the join between ua and f.

    Regarding using subqueries, here's a possible solution. It resembles my
    suggestion above.

    SELECT ue.EmailID, ue.ToAddress, ue.Subject, ue.Message,
    ue.SavedDate, ue.EmailKey, uaf.FileKey, uaf.FileName
    FROM UserEmails ue
    LEFT JOIN
    (SELECT f.FileKey, f.FileName, ua.EmailKey
    FROM UserEmailAttachments ua JOIN ProjFiles f
    ON ua.FileKey = f.FileKey) AS uaf
    ON ue.EmailKey = uaf.EmailKey
    WHERE ue.AdminID = 1
    AND ue.SavedDate > 1164344436
    AND ue.SentDate = 0

    Another solution using correlated subqueries. Highly subject to
    performance problems unless the outer query is likely to restrict its
    rows to a very small subset.

    SELECT ue.EmailID, ue.ToAddress, ue.Subject, ue.Message,
    ue.SavedDate, ue.EmailKey,
    (SELECT f1.FileKey FROM UserEmailAttachments ua1 JOIN ProjFiles f1
    USING (FileKey) WHERE ua1.EmailKey = ue.EmailKey) AS FileKey,
    (SELECT f2.FileName FROM UserEmailAttachments ua2 JOIN ProjFiles f2
    USING (FileKey) WHERE ua2.EmailKey = ue.EmailKey) AS FileName
    FROM UserEmails ue
    WHERE ue.AdminID = 1
    AND ue.SavedDate > 1164344436
    AND ue.SentDate = 0

    Note that MySQL 4.0 and earlier do not support subqueries.

    Regards,
    Bill K.
    Bill Guest

  11. #11

    Default Re: Why Is This Query A "Select_full_join"?

    Bill Karwin <com> wrote in
    news:newsguy.com:
     
    >
    > 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[/ref]

    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.

    Good Guest

  12. #12

    Default Re: Why Is This Query A "Select_full_join"?

    In article <196.97.131>, com
    says... 
    > >
    > > 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[/ref]
    >
    > 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.
    >[/ref]


    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 Guest

  13. #13

    Default Re: Why Is This Query A "Select_full_join"?

    TreeNet Admin <co.nz> wrote in
    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!
     

    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.
     

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

    Thanks




    Good Guest

Similar Threads

  1. Replies: 1
    Last Post: April 24th, 01:27 PM
  2. ColdFusion+cfquery+Oracle+CLOB+"Query of Query"
    By ferse in forum Coldfusion Database Access
    Replies: 0
    Last Post: April 25th, 01:28 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. CFINPUT type="radio" w/ "value" requires "label"
    By Iceborer in forum Macromedia ColdFusion
    Replies: 2
    Last Post: February 21st, 06:16 PM
  5. "Start" "Program" "Menu" list is empty
    By Pete in forum Windows XP/2000/ME
    Replies: 2
    Last Post: July 10th, 10:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139