巨大的返回设置mysql连接多个表一对多的关系

ok here is the question, is joining tables worth the huge return set for mysql for more than two tables?(for two tables yes maybe, but for more?) here is the situation :

好的,这里有一个问题,是加入表值得为两个表的mysql的巨大回报设置吗?(对于两个表是的,但是更多?)这里的情况如下:

table : main[
idmain (pk)
someotherid (key)
/*some other fields*/
]
table : main_sub[
idmainsub (pk)
idmain (key)
/*some other fields*/
]
table : main_sub2[
idmainsub2 (pk)
idmain (key)
/*some other fields*/
]
table : main_sub3[
idmainsub3 (pk)
idmain (key)
/*some other fields*/
]

so all the three main_sub tables have a one(main) to many(subs_tables) relationship, meaning that one row in main has multiple rows on the other tables. Here is the question :

所以这三个main_sub表都有一个(主)到多个(subs_tables)关系,这意味着main中的一行在其他表上有多行。这是一个问题:

SELECT * FROM `main`
INNER JOIN `main_sub` ON `main`.`idmain`=`main_sub`.`idmain` 
INNER JOIN `main_sub2` ON `main`.`idmain`=`main_sub2`.`idmain`
INNER JOIN `main_sub3` ON `main`.`idmain`=`main_sub3`.`idmain`
WHERE `main`.`someotherid`=1
vs.
SELECT * FROM `main` WHERE `main`.`someotherid`=1;
and then 
SELECT * FROM `main_sub` WHERE `main_sub`.`idmain`=(idmainfromfirstquery);
SELECT * FROM `main_sub2` WHERE `main_sub2`.`idmain`=(idmainfromfirstquery);
SELECT * FROM `main_sub3` WHERE `main_sub3`.`idmain`=(idmainfromfirstquery);

So is the join worth the huge result set and parsing, vs 4 queries? And with even miniature tables, all of the subs contain 5 links each to main, the size of the result set is getting out of control.

那么,对于4个查询,连接是否值得进行巨大的结果集和解析?即使是微型表,所有的子包含5个链接,每个链接到主,结果集的大小失控。

1 个解决方案

#1

One single query will be faster instead of looping:

一个查询将更快而不是循环:

SELECT * FROM `main`
INNER JOIN `main_sub` ON `main`.`idmain`=`main_sub`.`idmain` 
INNER JOIN `main_sub2` ON `main`.`idmain`=`main_sub2`.`idmain`
INNER JOIN `main_sub3` ON `main`.`idmain`=`main_sub3`.`idmain`
WHERE `main`.`someotherid`=1