mysql - 有多少个 "entities"在这个层次结构之下。嵌套集/邻接(英国境内有多少个 POI)

Question

Development language and DB: PHP/MySQL

我有一张 geo_places 表，其中包含大约 800 万个地理位置。

这些地方都是分层次的，我用

• parent_id (邻接)，
• lft/rgt (嵌套集)
• 和祖先(枚举)。

现在，我有一个名为 entities 的“兴趣点”表，它被分配到一个地理位置，我针对每个实体记录:

• lft geo_places 中位置的值
• 和地理位置的实际 ID。

现在我需要一种方法来提供目录列表，其中包含某个位置下的所有位置的 EFIFICNETLY 计数(但无论如何我都会缓存它)。

例如，如果我选择欧洲，那么我应该看到所有具有欧洲 parent_id 的地方，以及它下面的实体数量。请记住，一个地方不会直接分配给欧洲，但可能会分配给意大利的一个小村庄(它是欧洲的 child )。

你知道它是欧洲的 child ，因为:

• 意大利小村庄的lft值介于位置的lft和rgt值之间
• 或者因为祖先映射到这个地方。

例如，欧洲的祖先路径为 /1，ID 为 5。(1 表示“世界”)。然后意大利的小村庄会有 /1/5/234/28924/124128

其中 1 = 世界 5 = 欧洲 234 = 意大利 28924 = 贝加莫等...

无论如何，这就是我构建数据集的方式，并且我已经混合使用了层次结构以使我的查询更加高效(对于那些想知道为什么我支持嵌套集的人，邻接和枚举..这是因为我以这种方式获得了所有结构中最好的)。

这是我正在尝试做的一个例子。

我很灵活，可以根据需要更改我管理地点的方式。但是，这也是一个 Multi-Tenancy 应用程序，所以如果可以避免的话，我想尝试避免对 geo_places 进行保存计数。

简单来说:选择一个位置...然后显示所有将兴趣点分配给该位置或该位置的子位置的位置。

有什么建议吗？

Answer 1

这是一个解决方案:

select p.woeid, p.name, e.id, e.woeid, e.lft, count(e.lft) from
geo_places as p
 join  entities as e on e.lft >= p.lft and e.lft < p.rgt
where p.parent_woeid = 1
group by p.woeid

您可以将 1 替换为您要查找其后代的位置。

测试了 entities 中的 10 万个实体和 geo_places 中的 800 万行。lft 和 rgt 以及 woeid 上的索引。