some_id = other_id VS left join ?!

[T0PS3O]

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)?

[JamieC]

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.

[digitalpoint]

Agreed... use JOINS (always). :)

[T0PS3O]

OK, I guess I will then... That'll keep me off the streets for the next 9 months, recoding 100K sql queries.

[JamieC]

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. :)

[T0PS3O]

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?

[digitalpoint]

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%.

[JamieC]

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...

[T0PS3O]

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

[digitalpoint]

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. :)

[T0PS3O]

Then I'll be happy the day I find the server has crashed due to overload :)

[digitalpoint]

Hopefully when it gets anywhere remotely close to that point, you setup a MySQL Cluster (http://www.mysql.com/products/database/cluster/). :)

[T0PS3O]

I'll fly you in to come and install it then, since you're so psyched about it :)

[digitalpoint]

Hehe, okay. :)

[JamieC]

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. :)

Wow, that is a lot! What kind of box is it running on?!


Hopefully when it gets anywhere remotely close to that point, you setup a MySQL Cluster (http://www.mysql.com/products/database/cluster/). :)

I'd love to set up a cluster. Just a matter of convincing my employers that they need one... which they clearly dont :D

[digitalpoint]

Wow, that is a lot! What kind of box is it running on?!http://www.apple.com/xserve/

[JamieC]

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?! ;)

[digitalpoint]

My stuff is colocated here:
http://www.simplenet.com/about/data_center.html

The Xserves (2 of them) were what I used the donation money (http://www.digitalpoint.com/tools/donate.html) for earlier this year. I'm probably going to drop one or two more of them into the data center soon for extra load balancing and fail over.