selecting data after filtering out

Question

I am using RDBMS MySQL 5.7. I have 3 tables with the following structures:

Table A - primary table - products:

+----+-----------+-------------+------------+--------+
| id | title     | description | createdAt  | status |
+----+-----------+-------------+------------+--------+
|  1 | Product A | Desc A      | 2019-07-19 |      1 |
|  2 | Product B | Desc B      | 2019-07-04 |      1 |
+----+-----------+-------------+------------+--------+

Table B - product_highlights -> productId and highlightId are foreign keys.

+----+-----------+-------------+
| id | productId | highlightId |
+----+-----------+-------------+
|  1 |         1 |           9 |
|  2 |         1 |          10 |
|  3 |         1 |          11 |
|  4 |         2 |           9 |
|  5 |         2 |          10 |
|  6 |         2 |          11 |
|  7 |         2 |          12 |
+----+-----------+-------------+

Table C - product_categories -> productId and categoryId are foreign keys.

+----+-----------+------------+
| id | productId | categoryId |
+----+-----------+------------+
|  1 |         1 |          5 |
|  2 |         1 |          7 |
|  3 |         2 |          5 |
|  4 |         2 |          7 |
|  5 |         2 |          9 |
+----+-----------+------------+

I need to select categories and the number of their tours after the filtration process.
Currently, I am using a subquery for the filtration process then, selecting categories for each tour. I was wondering if any optimization can be applied to this query?

 SELECT
        pc.categoryId,
        COUNT(pc.productId)
    FROM
        product_categories pc,
        (
        SELECT
            t1.productId AS s1
        FROM
            product_categories t1
        JOIN
            product_highlights t2
        ON
            t1.productId = t2.productId
        WHERE
            t1.categoryId IN(7, 5) AND t2.highlightId IN(9, 10, 11)
        GROUP BY
            t1.productId
        HAVING
            2 * 3 = COUNT(
                DISTINCT t1.categoryId,
                t2.highlightId
            )
    ) productsIds
    WHERE
        pc.productId = productsIds.s1
    GROUP BY
        pc.categoryId

The resultset should be:

+------------+---------------------+
| categoryId | COUNT(pc.productId) |
+------------+---------------------+
|          5 |                   2 |
|          7 |                   2 |
|          9 |                   1 |
+------------+---------------------+

Answer 1

I gave it a quick look, but the query itself looks fine AFAIK tell. What I would suggest is adding a couple of indexes:

CREATE UNIQUE INDEX X1 ON product_categories (categoryId, productId);
CREATE UNIQUE INDEX X2 ON product_categories (productId, categoryId);

CREATE UNIQUE INDEX X4 ON product_highlights (productId, highlightId);

I would also recommend the following change for the tables:

CREATE TABLE products 
( productId int NOT NULL,  -- keep the name the same throughout the model

-- drop attribute id, what is the purpose of that?
CREATE TABLE product_categories 
( productId` int NOT NULL
, categoryId int NOT NULL
,   primary key (productId, categoryId) -- Skip index X2 if this is added

-- drop attribute id, what is the purpose of that?
CREATE TABLE product_highlights 
( productId int NOT NULL
, highlightId int NOT NULL
,    primary key (productId, highlightId) -- Skip index X4 if this is added