WHERE on SQL with JOIN on large tables
You can use NOT EXISTSas:

SELECT t2.rid
FROM t2
WHERE NOT EXISTS (
    SELECT 1 
    FROM t3
    WHERE t2.tag_id = t3.id
      AND t3.tag_name &lt;&gt; 'YY'
)
AND EXISTS (
    SELECT 1 
    FROM t3
    WHERE t2.tag_id = t3.id
      AND t3.tag_name &lt;&gt; 'XX'
);


Your index T2:index(tag_id) is already covered by T2:unique(tag_id,rid) som you can get rid of that

I don't work much with MySQL, but I get the impression that JOINs are often preferred over EXISTS/NOT EXISTS. Translating the query (Note the DISTINCT):

SELECT DISTINCT t2.rid
FROM t2
JOIN t3 AS t31
    ON t2.tag_id = t31.id
   AND t31.tag_name = 'XX'
LEFT JOIN t3 AS t32
    ON t2.tag_id = t32.id
   AND t32.tag_name &lt;&gt; 'YY'
WHERE t32.id IS NULL;