具有多个多对多连接的 MySQL 选择导致非常慢的查询

Question

使用下面的表结构:

项目(~20,000 条记录)

• item_id

属性(~30 条记录)

• 属性_id

Item_properties(约 40,000 条记录)

• 编号
• 属性_id
• item_id

用户可以选择通过 items 表本身中的多个字段来过滤项目，也可以选择项目必须具有的任意数量的 properties。搜索需要选择具有所有 属性的项目，而不仅仅是其中一个。我目前使用的格式

SELECT item.field...
FROM items
INNER JOIN item_properties AS ip1 ON ip1.item_id=item.item_id and ip1.property_id=3
INNER JOIN item_properties AS ip2 ON ip2.item_id=item.item_id and ip2.property_id=4
INNER JOIN item_properties AS ip3 ON ip3.item_id=item.item_id and ip3.property_id=5
INNER JOIN item_properties AS ip4 ON ip4.item_id=item.item_id and ip4.property_id=6
etc...
WHERE item.something_else='words'
GROUP BY item_id

我也尝试过，作为一种纯粹通过 WHERE 而不是 JOIN 指定搜索的方式

SELECT item.field...
FROM items
WHERE item.something_else='words'
and item_id IN (select item_id from item_properties where property_id=3)
and item_id IN (select item_id from item_properties where property_id=4)
and item_id IN (select item_id from item_properties where property_id=5)
and item_id IN (select item_id from item_properties where property_id=6)
etc...

然而，这种方法似乎需要更长的时间来查询集合。选择大约 4 个属性后，查询时间大约为 4-5 秒，更多，查询往往会被终止或使 MySQL 服务器完全崩溃。

据我所知，所有 _id 字段都在每个表上建立了索引，也是它们各自表的主键。

是否有改进查询的方法，或者我是否需要限制可查询的选项数量？

Answer 1

如果你想要所有的 property_id，请使用后聚合过滤

SELECT item.field
FROM items
INNER JOIN item_properties AS ip1 ON ip1.item_id=item.item_id and  
and ip1.property_id IN(3,4,5,6)
WHERE item.something_else='words'
GROUP BY item.field
HAVING COUNT(DISTINCT property_id )=4

4是property_id的个数IN(3,4,5,6)