which is better single query or multiple query??

Question

I do have 8 tables. when a query fires from search page data from all these 8 tables is pulled out and displayed on the result page. What I want to know is which is the best optimized query method for this process??

what I do now is :

$result = mysql_query("SELECT * FROM TG_dat,TG_dat1,TG_dat2,TG_dat3 WHERE 
TG_dat.web = TG_dat1.web AND TG_dat.web = TG_dat2.web AND TG_dat.web = 
TG_dat3.web AND TG_dat.web='".$uri."'")or die(mysql_error());

or do i need to use this??

$result = mysql_query("SELECT * FROM TG_dat WHERE web='$uri'")or die(mysql_error());
$result = mysql_query("SELECT * FROM TG_dat1 WHERE web='$uri'")or die(mysql_error());
$result = mysql_query("SELECT * FROM TG_dat2 WHERE web='$uri'")or die(mysql_error());
$result = mysql_query("SELECT * FROM TG_dat3 WHERE web='$uri'")or die(mysql_error());

Answer 1

Your existing query is perfect - always try to use as few queries as possible.

Answer 2

Don't worry so much about the two variations in query you've given - they'll perform more or less the same, but if performance is the issue then the first would be my choice - the single query involves a single round-trip to the server and it's easier to handle the results on the client. It's better to concern yourself with the SELECT *, SELECT <the fields you need> would make more sense - you don't need to have the web field returned multiple times in the results.

Answer 3

Less calls to the database is generally better, so you can use the first one.

However, rather than putting all of your tables directly into your FROM clause, it's generally considered good practice to use a join on related tables (which it appears these tables are).

For example:

"SELECT *
FROM TG_dat
LEFT JOIN TG_dat2 USING(web)
LEFT JOIN TG_dat3 USING(web)
WHERE TG_dat.web = '$uri'"

Answer 4

your existing query is good. The point is to limit querying db server for better effieciency