有什么比 MySQL 中的表连接更高效的方法吗？
我有一个具有实体属性值结构的表。举个例子，作为实体我可以有不同的国家。我可以具有以下属性：“位于”、“与……接壤”、“首都”。
然后我想找到所有“位于亚洲”并且“与俄罗斯接壤”的国家。最简单的方法是使用实体将表与其自身连接起来，然后使用 where 进行连接。
但是，如果实体列中有 20 行俄罗斯所在的行，那么在联合表中，我将有 20*20=400 行以俄罗斯为实体。每个国家都是如此。所以，联合表将会很大。
使用原始表提取所有位于亚洲的国家，然后提取所有与俄罗斯接壤的国家，然后使用位于两组国家中的元素，不是更有效吗？
I have a table with entity-attribute-value structure. As an example, as entities I can have different countries. I can have the following attributes: "located in", "has border with", "capital".
Then I want to find all those countries which are "located in Asia" and "has border with Russia". The straightforward way to do that is to join the table with itself using entities are the column for joining and then to use where.
However, if I have 20 rows where Russia in in the entity-column, than in the joint table I will have 20*20=400 rows with Russia as the entity. And it is so for every country. So, the joint table going to be huge.
Will it be not more efficient to use the original table to extract all countries which are located in Asia, then to extract all countries which have border with Russia and then to use those elements which are in both sets of countries?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您最终不应该拥有大量记录，因此这应该可行
You shouldn't end up having a huge number of records so this should work
您对连接与笛卡尔积感到困惑。连接中的行数不可能多于实际数据中的行数，唯一改变的是采用哪些元素/行。
因此，如果有 20 个俄语行，则连接生成的表中的俄语条目永远不会超过 20 个。
您建议使用的操作正是联接所做的操作。只需确保您有适当的索引，然后让 MySQL 完成其余的工作。
You are confusing join with Cartesian product. There could never be more rows in the join then there are in the actual data, the only thing being altered is which elements/rows are taken.
So if you have 20 Russian rows, the table resulting from the join could never have more than 20 Russian entries.
The operation you suggest using is exactly what a join does. Just make sure you have the appropriate indices and let MySQL do the rest.