1

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 |
+------------+---------------------+
  • Sidenote, you may want to rewrite the "," join to an ansi join – Lennart Jul 23 at 19:38
  • @Lennart, thanks! – Shahin Jul 24 at 17:39
  • Can you add your sample data as INSERT statements, and tables as CREATE TABLE statements, or create a db<>fiddle or similar? – Lennart Jul 24 at 17:46
  • @Lennart, Here is the sqlfiddle of my database structure and query. sqlfiddle.com/#!9/8c6036/1 – Shahin Jul 24 at 18:28
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
  • thank you for your help @Lennart. I will apply the composite primary key to my tables. – Shahin Jul 25 at 8:18
  • In addition, index X1 will probably be of use. – Lennart Jul 25 at 8:20
  • sure, I will give a try for sure. Thanks again! – Shahin Jul 25 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.