join vs. multiple queries

[Maximus]

I've only recently begun looking at mysql optimization and performance, and something that I can't find answered anywhere:  is a join/left-join/inner-join better than using multiple SELECT queries to extract information from 2-3 tables?  Especially since I often use multiple queries in conjunction with a while loop in PHP to retrieve info, so there are a lot of select queries happening.

 

And by "better" I am of course talking about performance.

 

Any help is appreciated!

[corbin]

It would depend on the situation, but if you are probably going to use the information from the other tables, it would be better in my opinion to do the joins.  Then again, the overhead of additional queries can be small compared to MySQL processing JOIN criteria, depending on the query.  It's hard to give a yes or no answer here.  What's the query/table schemas?

[Maximus]

Maybe this will clear it up a bit.  As you can see, I'm performing a sepperate query each time the loop iterates...other times its just a simple SELECT from one table and get an ID and use that to SELECT various things from another table.  So would JOIN be more appropriate for these things, or does it not really matter?

 

$query = mysql_query("SELECT `id`,`stockid`,`quantity` FROM `user_stocks` WHERE `userid`='$_COOKIE[userid]'");

while($g = mysql_fetch_array($query)) {

    $info = mysql_fetch_array(mysql_query("SELECT `name`,`tag`,`value` FROM `stocks` WHERE `id`='$g[stockid]' LIMIT 1"));

    //echo various info here

}

[teng84]

Maybe this will clear it up a bit.  As you can see, I'm performing a sepperate query each time the loop iterates...other times its just a simple SELECT from one table and get an ID and use that to SELECT various things from another table.  So would JOIN be more appropriate for these things, or does it not really matter?

 

$query = mysql_query("SELECT `id`,`stockid`,`quantity` FROM `user_stocks` WHERE `userid`='$_COOKIE[userid]'");

while($g = mysql_fetch_array($query)) {

    $info = mysql_fetch_array(mysql_query("SELECT `name`,`tag`,`value` FROM `stocks` WHERE `id`='$g[stockid]' LIMIT 1"));

    //echo various info here

}

 

in this case do the join ...