Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Does Join Order Matter?

    I am curious if the order in which I do Joins in a query affects performance?

    Here is my ERD...

    Code:
    SECTION_DIMENSION ->|-----||- DIMENSION -||-----|<- DIMENSION_SUBSECTION
    
    DIMENSION_SUBSECTION ->|------||- SUBSECTION

    I need to find all Sub-Sections for a given Section.

    Here is originally how I built my query... (Going from Right to Left)
    Code:
    SELECT sd.section_slug, sd.dimension_slug, ss.name
    FROM subsection AS ss
    INNER JOIN dimension_subsection AS ds
    ON ss.slug = ds.subsection_slug
    INNER JOIN section_dimension AS sd
    ON ds.dimension_slug = sd.dimension_slug
    WHERE sd.section_slug = 'finance'
    AND sd.dimension_slug = 'featured-finance'
    ORDER BY sd.section_slug, ss.sort

    And here is my new approach... (Going from Left to Right)
    Code:
    SELECT sd.section_slug, sd.dimension_slug, ss.name
    FROM section_dimension AS sd
    INNER JOIN dimension_subsection AS ds
    ON sd.dimension_slug = ds.dimension_slug
    INNER JOIN subsection AS ss
    ON ss.slug = ds.subsection_slug
    WHERE sd.section_slug = 'finance'
    AND sd.dimension_slug = 'featured-finance'
    ORDER BY sd.section_slug, ss.sort
    Is one approach better than the other?

    If so, why?



    (For this particular query, it won't matter because I only have a few records. But I wanted to ask, because down the road this could be an issue with larger data-sets!)

    Sincerely,


    Debbie

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,191
    Thanks
    80
    Thanked 4,563 Times in 4,527 Posts
    In theory, no. In practice, especially with MySQL, it can. (Much less so with SQL Server, which has a very powerful query optimizer built in.) Luckily, thanks to EXPLAIN and thanks to MySQL's extensions to standard SQL, you *can* force MySQL to do joins in a particular order if you determine that the order it chose was not optimal.

    Look here:
    http://dev.mysql.com/doc/refman/5.5/en/select.html
    and read
    STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in nonoptimal order. STRAIGHT_JOIN also can be used in the table_references list. See Section 13.2.9.2, “JOIN Syntax"
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    In theory, no. In practice, especially with MySQL, it can. (Much less so with SQL Server, which has a very powerful query optimizer built in.)
    So is this a case why some people still consider MySQL to be a "small time" database?

    (I guess this would be less likely to happen in Oracle...)


    Luckily, thanks to EXPLAIN and thanks to MySQL's extensions to standard SQL, you *can* force MySQL to do joins in a particular order if you determine that the order it chose was not optimal.
    So in the example I gave, what would I want to look for in comparing the EXPLAIN from Query #1 to Query #2?

    Also, I guess my larger (implied) question was "Why would I want to structure things like Query #1 or Query #2?"

    The convention that I have usually used is going from left-to-right, and typically starting with the One/Parent table and linking to the Many/Child table, and then following the daisy-chain to join everything.

    In cases where I have several "feeder" tables (e.g. Section, Dimension, SubSection) going into a Junction Table (e.g. Article_Placement), I typically start with the "hub" table in the FROM, and then join each "feeder" table to it.

    I don't have any empirical data to bad up these conventions, they just seem the most natural to me.

    Of course, I am always looking to "work smarter"!!

    Sincerely,


    Debbie

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,191
    Thanks
    80
    Thanked 4,563 Times in 4,527 Posts
    Generally speaking, you want the WHERE condition that is the *MOST EFFECTIVE* in limiting the number of results to occur first.

    In the case of a standard one-to-many relationship, this almost always means you want to limit the matching "ones" (e.g., say "categories") so that when you join to the "many" (e.g., "subcategories") you will pull in the fewest possible records from the "many".

    And MySQL will usually do this just fine, no matter which order you put the JOINs in.

    It really is the exceptional case where MySQL might choose the wrong ordering.

    And, as I said, EXPLAIN will almost always point this out to you, once you understand how to read what it says.

    In all the MySQL coding I have done, I only remember two case where I used STRAIGHT_JOIN to override MySQLs choice, and both of those involved sub-queries where MySQL mis-estimated the number of records that would be returned from the sub-query.

    In short, for 98% of all queries, don't worry about it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Generally speaking, you want the WHERE condition that is the *MOST EFFECTIVE* in limiting the number of results to occur first.
    So I believe that would make Query #2 more efficient, because I start with the table with the "Section" in it. (i.e. most general)

    phpMyAdmin showed Query #1 at like 0.0006 seconds and Query #2 at 0.0005 seconds, which could confirm I made the right choice, but with a record pool of THREE, I wouldn't bet the farm on that just yet!!


    And, as I said, EXPLAIN will almost always point this out to you, once you understand how to read what it says.
    That one is "work in progress"...


    In all the MySQL coding I have done, I only remember two case where I used STRAIGHT_JOIN to override MySQLs choice, and both of those involved sub-queries where MySQL mis-estimated the number of records that would be returned from the sub-query.

    In short, for 98% of all queries, don't worry about it.
    Okay, so I won't worry about it for this instance!

    As always, thanks!!

    Sincerely,


    Debbie

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,191
    Thanks
    80
    Thanked 4,563 Times in 4,527 Posts
    Don't be surprised if EXPLAIN gives you the same results for #1 and #2, indicating that almost surely MySQL has made the right choices. But also don't be surprised if MySQL opts to do an "all" because it realizes there are only 3 records and so EXPLAIN doesn't really tell you anything.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Don't be surprised if EXPLAIN gives you the same results for #1 and #2, indicating that almost surely MySQL has made the right choices. But also don't be surprised if MySQL opts to do an "all" because it realizes there are only 3 records and so EXPLAIN doesn't really tell you anything.
    Just ran a test.

    Yep. Everything is *identical* between the two.

    (I'm sure this question would have been of more value had I been joining Tables with tens or hundreds of thousands of records, but I was curious while the issue was in front of me?!)


    Debbie


  •  

    Posting Permissions

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