1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

some_id = other_id VS left join ?!

Discussion in 'MySQL' started by T0PS3O, Oct 10, 2005.

0
  1. #1
    Can someone please xplain to me the fundamental difference between just cross referencing two tables with a common id like:

    SELECT d.name FROM description d, info i WHERE d.id = i.id and i.id = '1';

    And then left joining something like:

    SELECT d.name FROM description d LEFT JOIN info i ON d.id = i.id WHERE i.id = '1';

    I've always used the first option myself, but today I encountered a situation where it didn't work as expected in a particular part of code so I had to use the join method, though I can't picture the fundamental difference to be honest.

    Anyone willing to educate me in not-so-geeky terms (that's what I hate about MySQL documentation, the lingo isn't clear to me)?
     
    T0PS3O, Oct 10, 2005 IP
  2. JamieC

    JamieC Well-Known Member

    Messages:
    226
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    138
    #2
    The first option is TSQL, the second is proper SQL. Using joins allows the database to make an informed decision about what you actually want it to do, and is much less resource intensive.

    Edit:

    Sorry that post was horribly uninformative. I strongly suggest you visit http://www.w3schools.org and peruse their SQL section, where they expain joins fully. TSQL (Transact-SQL is a bit of a hybrid that was supposed to make the language more intuitive.) Realistically, you should always use joins, there is never a situation where you should use TSQL.
     
    JamieC, Oct 10, 2005 IP
  3. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #3
    Agreed... use JOINS (always). :)
     
    digitalpoint, Oct 10, 2005 IP
  4. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #4
    OK, I guess I will then... That'll keep me off the streets for the next 9 months, recoding 100K sql queries.
     
    T0PS3O, Oct 10, 2005 IP
  5. JamieC

    JamieC Well-Known Member

    Messages:
    226
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    138
    #5
    LOL, I wouldn't bother rewriting the existing ones - if it ain't broke and all that...

    It's probably not worth your time - additional processing capacity would probably be cheaper than what your time's worth. Just use JOIN on any new queries. :)
     
    JamieC, Oct 10, 2005 IP
  6. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Good point. :)

    By the way, I've looking at the server stats, just wondering, what is a modern machine capable of doing in terms of queries a second / data transfer?

    I've now got one live site and a dev site on there, together doing 0.61 queries a second on average. But I'm going to shift 6 more live sites on there which get tons and tons of traffic.

    How many queries on average per sec/hour and data are yours doing?
     
    T0PS3O, Oct 10, 2005 IP
  7. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #7
    You aren't going to be anywhere remotely close to the limit. :) I've clocked my primary MySQL server at around 4,000 queries a second. Under real-world use it only does about 120 queries per second 24/7 and it's CPU load runs at about 3-4%.
     
    digitalpoint, Oct 10, 2005 IP
  8. JamieC

    JamieC Well-Known Member

    Messages:
    226
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    138
    #8
    As the man says, I really wouldn't worry about it.

    We run a few dedicated servers, most of them pretty cheap single processor Athlons. Our current configuration is mySQL on one, dedicated machine and I don't think it even notices :). Just checked and we seem to average around 3 - 5 queries a second.

    Are you running mySQL on your webserver? Do you run Windows or *nix? I'm just interested to see what sort of CPU load you get if you run a webserver on the same machine...
     
    JamieC, Oct 11, 2005 IP
  9. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #9
    I'm not worried, just curious. I predict it will go to about 25 a second by next month :cool:

    I run RHEL v3 and MySQL on the same box, an Athlon 64 1.8Ghz (highest load I've seen is 1% this far :D ) with a GB Ram.

    Moving from shared hosting to dedicated is sooo exciting :D
     
    T0PS3O, Oct 11, 2005 IP
  10. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #10
    You'll be fine... :) My primary MySQL server (mentioned in previous post) is the one that runs all the SQL queries for this forum, the ad network, the keyword tracker and a geotargetting system, and it could easily run 20x as much query traffic that it currently does. :)
     
    digitalpoint, Oct 11, 2005 IP
  11. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #11
    Then I'll be happy the day I find the server has crashed due to overload :)
     
    T0PS3O, Oct 11, 2005 IP
  12. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #12
    Hopefully when it gets anywhere remotely close to that point, you setup a MySQL Cluster. :)
     
    digitalpoint, Oct 11, 2005 IP
  13. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #13
    I'll fly you in to come and install it then, since you're so psyched about it :)
     
    T0PS3O, Oct 11, 2005 IP
  14. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #14
    Hehe, okay. :)
     
    digitalpoint, Oct 11, 2005 IP
  15. JamieC

    JamieC Well-Known Member

    Messages:
    226
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    138
    #15
    Wow, that is a lot! What kind of box is it running on?!


    I'd love to set up a cluster. Just a matter of convincing my employers that they need one... which they clearly dont :D
     
    JamieC, Oct 11, 2005 IP
  16. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #16
    digitalpoint, Oct 11, 2005 IP
  17. JamieC

    JamieC Well-Known Member

    Messages:
    226
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    138
    #17
    I love the way they even make their servers look good. Anyway, no wonder you're only getting a CPU load < 10%! ;)

    Fantastic box... you got it colo or is digital point packing multiple redundant OC3s to each of the 7 continents?! ;)
     
    JamieC, Oct 11, 2005 IP
  18. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #18
    digitalpoint, Oct 11, 2005 IP