Reports

MYSQL decides the relevant strategy: MATERIALIZATION (or) DUPSWEEDOUT based on the cost even if we force it (according to MySQL manuals for SEMIJOIN Hint). On the other hand, there is another Hint SUBQUERY, which you can use since nested subqueries are present in your query, which uses the materialization strategy.

The modified code below will use the MATERIALIZATION strategy:

EXPLAIN FORMAT=TREE
SELECT /*+ **SUBQUERY(@subq2 MATERIALIZATION)** */ rse.*
FROM rubric_session_elements rse
WHERE rse.rubric_session_id IN (
    SELECT /*+ QB_NAME(subq2) */ rs.id
    FROM rubric_sessions rs
    WHERE rs.template_id IN (
        SELECT /*+ QB_NAME(subq1) */ a.rubric_template_id
        FROM activities a
        WHERE a.group_id = 123
    )
);

Please let me know if you need more information.

79174664