EverSQL Database Performance Knowledge Base

Question 1

The fastest SQL expression for complex searching in many-to-many relationships?

In a product_tag table, the columns are

id, product_id, tag_id

If I would like to search for a product that is tag1 OR tag2 OR tag3, the direct way is:

SELECT DISTINCT productId FROM product_tags WHERE tagId IN (2,4);

If I would like to search for a product that is tag1 AND tag2 AND tag3, the direct way is:

SELECT productId FROM product_tag WHERE tag_id IN (tag1, tag2, tag3) GROUP BY productId HAVING COUNT(*) = 3

But the question is if I would like to search a product that has a complex tag relationship, such as:

product that is (tag1 OR tag2 OR tag3) AND (tag 4 OR tag5 OR tag 6) AND (tag 7 OR tag8 OR tag9)

What is the SQL expression with best performance? (and preferably elegant).

Edit:
The most important performance gain was to add indexes, as Remus in the comments recommended.

Answer

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

ALTER TABLE `product_tags` ADD INDEX `product_tags_idx_tagid` (`tagId`);

SELECT
        DISTINCT product_tags.productId 
    FROM
        product_tags 
    WHERE
        product_tags.tagId IN (
            2, 4
        )

Question 2