hd3tw5ce

1SELECT
2    pc.categoryId,
3    COUNT(pc.productId)
4FROM
5    product_categories pc
6INNER JOIN
7    (
8    SELECT
9        t1.productId AS tmpProductId
10    FROM
11        product_categories t1
12    JOIN
13        product_highlights t2
14    ON
15        t1.productId = t2.productId
16    WHERE
17        t1.categoryId IN(7, 5) AND t2.highlightId IN(9, 10, 11)
18    GROUP BY
19        t1.productId
20    HAVING
21        2 * 3 = COUNT(
22            DISTINCT t1.categoryId,
23            t2.highlightId
24        )
25) productsIds
26ON
27    pc.productId = productsIds.tmpProductId
28GROUP BY
29    pc.categoryId 
30     
31CREATE TEMPORARY TABLE IF NOT EXISTS tempProducts(
32    tmpProductId SMALLINT NOT NULL,
33    PRIMARY KEY(tmpProductId)
34)
35SELECT 
36    t1.productId AS tmpProductId
37FROM
38    product_categories t1
39JOIN
40    product_highlights t2
41ON
42    t1.productId = t2.productId
43WHERE
44    t1.categoryId IN(7, 5) AND t2.highlightId IN(9, 10, 11)
45GROUP BY
46    t1.productId
47HAVING
48    2 * 3 = COUNT(
49        DISTINCT t1.categoryId,
50        t2.highlightId
51    ); 
52     
53SELECT
54    pc.categoryId,
55    COUNT(pc.productId)
56FROM
57    product_categories pc
58INNER JOIN
59    tempProducts
60ON
61    pc.productId = tempProducts.tmpProductId
62GROUP BY
63    pc.categoryId 
64     
65+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+
66| id | select_type | table        | partitions | type  | possible_keys            | key              | key_len | ref                          | rows | filtered | Extra                                        |
67+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+
68|  1 | SIMPLE      | tempProducts | NULL       | index | PRIMARY                  | PRIMARY          | 2       | NULL                         |    2 |   100.00 | Using index; Using temporary; Using filesort |
69|  1 | SIMPLE      | pc           | NULL       | ref   | PRIMARY,unq_catid_prodid | unq_catid_prodid | 2       | rr.tempProducts.tmpProductId |    2 |   100.00 | Using index                                  |
70+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+