MySQL 查询以确定与类别 A 和 B 相关的记录

Question

这是我的数据的简化版本:

products:
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Product X |
|  2 | Product Y |
|  3 | Product Z |
+----+-----------+

categories:
+----+---------------+
| id | name          |
+----+---------------+
|  1 | Hotel         |
|  2 | Accommodation |
+----+---------------+

category_product
+----+------------+-------------+
| id | product_id | category_id |
+----+------------+-------------+
|  1 |          1 |           1 |
|  2 |          1 |           2 |
|  3 |          2 |           1 |
|  4 |          3 |           2 |
+----+------------+-------------+

如何构建一个高效的查询，只检索具有两个类别“酒店”和“住宿”相关的产品(例如产品 X)？

我首先尝试了一种连接方法

SELECT *
FROM products p
JOIN category_product cp
ON p.id = cp.product_id
WHERE cp.category_id = 1 OR cp.category_id = 2

^ 这不起作用，因为它没有将查询限制为同时包含。

我找到了一种使用子查询的有效方法...但是出于性能原因我被警告不要使用子查询:

SELECT *
FROM products p
WHERE
(
    SELECT id
    FROM category_product
    WHERE product_id = p.id
    AND category_id = 1
)
AND
(
    SELECT id
    FROM category_product
    WHERE product_id = p.id
    AND category_id = 2
)

是否有更好的解决方案(或者替代方案如何)？我考虑过将类别去规范化到产品的额外列中，但理想情况下希望避免这种情况。希望有 Elixir 的解决方案!

更新

我已经运行了答案中提供的一些(很棒的)解决方案:我的数据是 235 000 category_product 行和 58 000 产品，显然基准总是依赖于环境和索引等。

“关系部门”@podiluska

2 categories: 2826 rows  ~ 20ms 
5 categories: 46 rows ~ 25-30 ms 
8 categories: 1 rows ~ 25-30 ms 

“哪里存在”@Tim Schmelter

2 categories: 2826 rows  ~ 5-7ms 
5 categories: 46 rows ~ 30 ms 
8 categories: 1 rows ~ 300 ms 

可以看到结果随着类别数量的增加而开始出现差异。我将考虑使用“关系划分”，因为它提供了一致的结果，但实现可能会导致我也查看“存在的地方”(长格式 http://pastebin.com/6NRX0QbJ )

Answer 1

SELECT p.*
FROM products p
     inner join 
(
    select product_ID
    from category_product
    where category_id in (1,2)
    group by product_id
    having count(distinct category_id)=2
) pc
    on p.id = pc.product_id

这种技术叫做“关系划分”