Mysql products in category select

Question

I have a table called product_cats corresponding columns

product_id,
cat_id

Say we have categories 1, 2, 3, 4, and 5.

I need all product_id in cat_id 1 and 2 and (3 or 4 or 5).

At the moment, I have several working methods, but all of them are very slow.

Can someone shed mysql magic on me?

Edit:

Thus, the product should be in categories 1 and 2, as well as in 3, 4 or 5.

Here is one of my attempts to better understand the idea.

SELECT
  p1.product_id
FROM
  product_cats p1 JOIN product_cats p2 ON ( p2.product_id = p1.product_id )  
  JOIN product_cats p3 ON ( p3.product_id = p1.product_id )  
  JOIN product_cats p4 ON ( p4.product_id = p1.product_id )   
WHERE
  (p1.category_id=? AND p2.category_id=? AND p3.category_id=?)
  OR 
  (p1.category_id=? AND p2.category_id=? AND p4.category_id=?)

Answer 1

Using Mysql, you can implement your logic using functions group byand sumwith a sentencehaving

select product_id
from product_cats
group by product_id
having sum(cat_id = 1) > 0
and sum(cat_id = 2) > 0
and sum(cat_id in(3,4,5)) > 0

product_ids, cat_id 1 cat_id 2, cat_id 3,4,5

Answer 2

, 1 2, :

select
  product_id
from
 product_cats
where
  cat_id in (1,2)
group by
  product_id
having
  count(distinct cat_id)=2;

product_id, cat_id :

alter table product_cats add index idx_product_cats (product_id, cat_id);