Joins vs Multiple Queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Joins vs Multiple Queries

Post by Chalks »

These three things produce the same result (unless I did something stupid):

Code: Select all

// option A
$sql = "SELECT id FROM users WHERE name='$name'";
// get that id, assign it to variable $uid
$sql = "SELECT id FROM settings WHERE uid=$uid";
// get that id, assign it to variable $setid
return $setid

Code: Select all

// option B
$sql = "SELECT settings.id FROM settings JOIN users ON settings.uid = users.id WHERE users.name='$name'";
// get that id, assign it to variable $setid
return $setid

Code: Select all

// option C
$sql = "SELECT settings.id FROM settings, users WHERE settings.uid = users.id AND users.name='$name'";
// get that id, assign it to variable $setid
return $setid

Which of these is easier for you to read, and which is actually faster / more efficient?
Last edited by Chalks on Fri May 28, 2010 2:34 pm, edited 1 time in total.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Joins vs Multiple Queries

Post by Eran »

There's no global answer, it's very situation specific. In most cases, using joins is more performant and easier to maintain - especially since you get all the data in one result set, it's troublesome to merge data in multiple rows (especially when it's not a 1-to-1 relationship).
In some cases, multiple joins can cause degraded performance if the query is heavy to compute. It's very dependent on the schema and indexing strategy used. In those cases, it's better to separate some part of the joins to separate queries.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Joins vs Multiple Queries

Post by Weirdan »

Chalks wrote:These three things produce the same result (unless I did something stupid)
Second and third options lack filtering on name column.
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Re: Joins vs Multiple Queries

Post by Chalks »

Weirdan wrote:Second and third options lack filtering on name column.
Whoops... fixed.
pytrin wrote:In some cases, multiple joins can cause degraded performance if the query is heavy to compute. It's very dependent on the schema and indexing strategy used. In those cases, it's better to separate some part of the joins to separate queries.
What sort of indexing strategy are you talking about? The internal mysql indexing, or whether I have the keys set up properly in the schema? I only know enough to set up a proper relational database (in 3rd normal form). Anything more complicated than that and I quickly start to get lost.

Also: assume that there are 10,000+ records in each table and that the relation is 1-to-1 (for this exercise at least). Does that change your answer at all?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Joins vs Multiple Queries

Post by Eran »

What sort of indexing strategy are you talking about? The internal mysql indexing, or whether I have the keys set up properly in the schema?
I don't know what is the "internal mysql indexing", but by indexing strategy I mean which columns you choose to have as indexes in order to optimize the queries you run.
Does that change your answer at all?
Allow me to rephrase my answer: I would always use joins if there is a clear relationship between tables involved. I might later separate some joins to simpler queries if there is a performance need, but that rarely happens.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Joins vs Multiple Queries

Post by Weirdan »

Chalks wrote:Which of these is easier for you to read, and which is actually faster / more efficient?
For me the third option would be easiest to read if you spelled out join type. I always forget what 'JOIN' alone means: 'INNER JOIN' or 'LEFT JOIN'. As for speed, all of those option would give roughly the same performance, given that you have indexes on name, uid and id columns and do not execute second query of the first option in a loop.
Chalks wrote:Also: assume that there are 10,000+ records in each table and that the relation is 1-to-1 (for this exercise at least). Does that change your answer at all?
10 thousands of records is not a lot.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Joins vs Multiple Queries

Post by Benjamin »

The order in which tables are joined can have a dramatic impact on performance. In this case, it's a tie between B and C. C is the better method however, because it allows the MySQL optimizer to select the best join order. I wasn't able to find a lot of information on this, but if I recall correctly, explicitly using JOIN clauses forces the table join order.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Joins vs Multiple Queries

Post by Weirdan »

Benjamin wrote:I wasn't able to find a lot of information on this, but if I recall correctly, explicitly using JOIN clauses forces the table join order.
It's not, you have to use STRAIGHT_JOIN to force join order.

Edit: well, LEFT JOIN is forcing the order, but we're speaking about INNER JOIN here.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Joins vs Multiple Queries

Post by Benjamin »

I was told this by a DBA years ago. Maybe he didn't get into specifics to keep it simple.
Post Reply