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