SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    use of straight_join

    Hi

    I've recently been going through all my queries running EXPLAIN on them and have found that on a large number of them i've had to use 'select straight_join'. This this normal to have to use it for 4/5 queries? i suppose this depends on the types of queries im running but im relatively new to mysql so just worried im doing something horribly wrong..

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sheardben View Post
    ...i've had to use 'select straight_join'.
    what led you to the conclusion that you "had" to do that?

    the manual issues this caution:
    STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.
    if you are new to mysql i will venture to guess that you are not using STRAIGHT_JOIN correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that - im pretty sure im not using it correctly.

    I read that section in the manual (realised now i missed the bit that says these cases are few) and used it because when using EXPLAIN on the query i found that it was performing the joins in the wrong order and in doing so requiring an entire scan of the first table. I put straight_join in an it seemed to fix the problem - the joins were done in the order i wanted which as far as i can see is the best order.
    I've never known that much about the different joins - find all the different types of joins very confusing.
    I normally just use JOIN if it's making a join where there must be a matching row and LEFT JOIN if there doesn't need to be a matching row and thus make the values of the columns in that table null. (if that makes any sense...)

    If you know of any good tutorials on using the different joins that would be great!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sheardben View Post
    If you know of any good tutorials on using the different joins that would be great!
    yup, here's one...

    http://articles.sitepoint.com/articl...he-from-clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •